3 Value Logic to handle NULL in all kind of Database

3 value logic (TRUE, FALSE, UNKOWN)  in database is contained to give support to NULL, we generally perceived of TRUE or FALSE, but let see how null takes part in Boolean table of OR and AND

In below OR binary logic table, let’s focus on Unknown…

·          Unknown AND True = Unknown

·          Unknown AND False = False

·          Unknown AND Unknown = Unkown

AND	True	False	Unknown
True	True	False	Unknown
False	False	False	False
Unknown	Unknown	False	Unknown

Now let’s take OR and focus on Unknown

Unknown OR True = True

Unknown OR False = Unknown

Unknown OR Unknown = Unknown

OR	True	False	Unknown
True	True	True	True
False	True	False	Unknown
Unknown	True	Unknown	Unknown

Now let’s do a practical, here I am creating #temp1  table which hold 1 to 5 numeric value

select top 5 ROW_NUMBER() over (order by name) as id into #temp1  from sys.objects

In second sql, I am creating another table #temp2 which hold numeric value from 1 to 3 and another value of NULL.

select * into #temp2 from

(

select top 3 row_number() over (order by name) as id from sys.objects

union

select null as id

) a

select * from #temp1;

id

--------------------

1

2

3

4

5

(5 row(s) affected)

select * from #temp2

id

--------------------

NULL

1

2

3

(4 row(s) affected)

Now let run the IN query

select 'wow!', id from #temp1 where id in (select id from #temp2)

which will be internally evaluated as

select 'wow!', id from #temp1 where id = NULL or id = 1 or id = 2 or id = 3

lets revisit OR table then decode the where condition

OR	True	False	Unknown
True	True	True	True
False	True	False	Unknown
Unknown	True	Unknown	Unknown

here if we decode where clause we will find

where  id = NULL or id = 1 or id = 2 or id = 3

for id = 1,

where  Unkown  OR  True OR False OR False, this will result to TRUE (according to the OR table below)

for id =2,

where  Unkown  OR  False OR True  OR False, this will result to TRUE (according to the OR table below)

for id =3,

where  Unkown  OR  False OR False  OR True, this will result to TRUE (according to the OR table below)

for id =4,

where  Unkown  OR  False OR False  OR False, this will result to FASE (according to the OR table below)

for id =5,

where  Unkown  OR  False OR False  OR False, this will result to FASE (according to the OR table below)

So the result of

select 'wow!', id from #temp1 where id in (select id from #temp2)

     id

---- --------------------

wow! 1

wow! 2

wow! 3

(3 row(s) affected)

Now let’s evaluated NOT IN

select 'wow!', id from #temp1 where id not in (select id from #temp2)

it will internally evaluated as

select 'wow!', id from #temp1 where id <> null and id <> 1 and id <> 2 and id <> 3

Now lets revisit AND table and evaluate where condition

AND	True	False	Unknown
True	True	False	Unknown
False	False	False	False
Unknown	Unknown	False	Unknown

here if we decode where clause we will find

where  id <> NULL AND id = 1 AND id = 2 AND id = 3

for id = 1,

where  Unkown  AND  True AND False AND False, this will result to UNKNOWN (according to the AND table below)

for id =2,

where  Unkown  AND  False AND True  AND False, this will result to UNKNOWN (according to the AND table below)

for id =3,

where  Unkown  AND  False AND False  AND True, this will result to UNKNOWN (according to the AND table below)

for id =4,

where  Unkown  AND  False AND False  AND False, this will result to UNKNOWN (according to the AND table below)

for id =5,

where  Unkown  AND  False AND False  AND False, this will result to UNKNOWN (according to the AND table below)

 So the result of

select 'wow!', id from #temp1 where id not in (select id from #temp2)

     id

---- --------------------

(0 row(s) affected)

Enjy coding…SQL J

Post Reference: Vikram Aristocratic Elfin Share

How the object is handled when inplace=True is passed vs. when inplace=False, in Pandas dataframe

Let’s understand inplace with below code block where it returns None df3

df3=df1.set_index('account_no',inplace=True, drop=False)
#df3.set_index('account_no',inplace=False, drop=True)
print("Index value of DF3 : \n", df3)

Set_index: set_index method of dataframe is used to set the index of dataframe using existing column or array.

Syntax: dataframe.set_index(key,append=False, inplace =False,drop=True)

Let play with the datasource of

Let’s understand two important parameter inplace and drop.

When inplace is set to “True”  it tells change need to be done in original dataframe and nothing gets returned as object

Whereas when inplace is set to “False” it changes in the copy of dataframe and returned as object without affecting original dataframe.

Lets check with example

#inplace is set to True
df3=df1.set_index('account_no',inplace=True, drop=False)
#df3.set_index('account_no',inplace=False, drop=True)
print("DF3 with inplace set to True : \n", df3)

print("\nDF1 Data : \n", df1)

Output:

DF3 with inplace set to True :

 None

DF1 Data :

             account_no  branch  city_code customer_name  amount

account_no                                                    

2112              2112  3212.0      321.0       Sidhika   19000

2119              2119     NaN      215.0      Prayansh   12000

2115              2115  4321.0      212.0       Rishika   15000

2435              2435  2312.0        NaN      Sagarika   13000

2356              2356  7548.0      256.0           NaN   15000

So here we can see the output of DF3 is None because df1.set_index uses parameter inplace=”True” which says that changes need to be done in original dataframe and returns nothing.

df3=df1.set_index('account_no',inplace=True, drop=False)

Now lets change the inplace to Flase, by definition it says that it should change in the copy of dataframe object and returns an df object

#inplace is set to True
df3=df1.set_index('account_no',inplace=False, drop=False)
#df3.set_index('account_no',inplace=False, drop=True)
print("DF3 with inplace set to False : \n", df3)

print("\nDF1 Data : \n", df1)

Output:

DF3 with inplace set to False :

             account_no  branch  city_code customer_name  amount

account_no                                                    

2112              2112  3212.0      321.0       Sidhika   19000

2119              2119     NaN      215.0      Prayansh   12000

2115              2115  4321.0      212.0       Rishika   15000

2435              2435  2312.0        NaN      Sagarika   13000

2356              2356  7548.0      256.0           NaN   15000

DF1 Data :

    account_no  branch  city_code customer_name  amount

0        2112  3212.0      321.0       Sidhika   19000

1        2119     NaN      215.0      Prayansh   12000

2        2115  4321.0      212.0       Rishika   15000

3        2435  2312.0        NaN      Sagarika   13000

4        2356  7548.0      256.0           NaN   15000

With the output we can see the changes are done in copy of original dataframe without affecting the original one.

Complete Program:

import pandas as pd
import numpy as np

df1 = pd.read_csv("NullFilterExample.csv")

print('Original DF rows \n',df1 , '\n')

#inplace is set to False
df3=df1.set_index('account_no',inplace=False, drop=False)
#df3.set_index('account_no',inplace=False, drop=True)
print("DF3 with inplace set to False : \n", df3)

print("\nDF1 Data : \n", df1)


#inplace is set to True
df4=df1.set_index('account_no',inplace=True, drop=False)
#df3.set_index('account_no',inplace=False, drop=True)
print("DF3 with inplace set to True : \n", df4)

print("\nDF1 Data : \n", df1)

Data Science with…Python J

Post Reference: Vikram Aristocratic Elfin Share