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

Character family datatype and NULL in Union statement

Conclusion: The default datatype of NULL is INT (wait lets first prove it, we might be wrong).

Yesterday my teammate called me and showed me a strange behavior of NULL with character datatype in union clause (in Teradata database) and this strange behavior is applicable to most of databases (NOT in SQL Server  J). Let’s try to stimulate the same scenario.

Here we have below Union statement

select 'a' as col1, 'b' as col2, 100 as col3, 200 as col4

union

select 'x' as col1, 'y' as col2, null, null

col1 col2 col3        col4

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

a    b    100         200

x    y    NULL        NULL

(2 row(s) affected)

There are four columns in query, first two columns are of character family datatype and col3 and col4 are from Integer family datatype.

Now look at the second query

select 'x' as col1, 'y' as col2, null, null

Here, notice that first two columns are of char datatype and rest two columns are of NULL and union worked perfectly.

Lets revise the union rule:

1.        Column number should match in all select query involved in union

2.        The datatype of column in one select query should match with other select query participating in UNION.

Now return back to our first select statement in UNION query

select 'a' as col1, 'b' as col2, 100 as col3, 200 as col4

So by Union rule book, any query doing union with above query should have first two columns as character datatype and last two columns as INT datatype.

Now if you see our second query

select 'a' as col1, 'b' as col2, 100 as col3, 200 as col4

union

select 'x' as col1, 'y' as col2, null, null

First two columns are of character datatype which matches with the datatype of first query and unlike first two columns, last two are NULL which doesn’t match with the datatype of first query col3 and col4 datatype. Still query work fine.

There could be two reason why query worked fine:

1.  NULL is compatable with INT datatype
2.             The is an implicit conversion happening with NULL and INT datatype, something like this ( cast( NULL as INT)

Let dig it further by rewriting the query

select 'a' as col1, 'b' as col2, 100 as col3, 200 as col4

union

select 'x', NULL, NULL, NULL

Above query fails, that could means

1. Compiler is able to do implicit conversion of NULL to character database
2. NULL is not compatible to Character family datatype.

Now let’s rewrite the query and cast the NULL in second column of second query

select 'a' as col1, 'b' as col2, 100 as col3, 200 as col4

union

select 'x', cast (NULL as varchar(1)), NULL, NULL

col1 col2 col3        col4

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

a    b    100         200

x    NULL NULL        NULL

The query work fine.

We can inference from above set of query that NULL is compatible with INT but not with CHAR datatype.

Let find out the root clause of this.

The question must be in your mind must be: Does NULL has any DEFAULT datatype? Lets try to find out this answer.

Here I am storing result of query in temporary table #tempotable

select null as col1 into #tempotable

Lets now see the table and column property of #tempotable

TABLE_QUALIFIER      TABLE_OWNER   COLUMN_NAME   TYPE_NAME     PRECISION     LENGTH

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

tempdb               dbo            col1          int           10            4

J here we can see Datatype of NULL is treated as INT Type with 4 byte length.

What we concluded, default datatype of NULL is INT??

Wait before concluding anything so early J

Let declare a variable of variant type and assign it with NULL

declare @var sql_variant = NULL

Now let find out the datatype of @Var variable

select sql_variant_property(@var, 'Basetype') as TypeName,

     sql_variant_property(@var, 'Precision') as Precision,

Output:

Type Name    Precision

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

NULL              NULL               

Now this shows NULL has no datatype J

What we concluded: NULL has no datatype, but when it takes part in forming result set, the compiler consider NULL as INT datatype with 4 byte length and 10 precision.

NOTE: SQL Server 2005 had this issue, in all later versions they rectified this issue and have high degree of NULL handling Enjy coding…SQL J

Post Reference: Vikram Aristocratic Elfin Share