NULL is introduced by EF Code in relational database model in 1975. It is used to specifically mention that data value does not exists. Code used greek letter lower Omega to represent NULL in database. NULL does not means ‘Nothing’ or Zero. For example , while answering a question – How many pencils Mary has in her bag ? The answer can be 1,3,0 or ‘Don’t Know’. Here ‘0’ and ‘don’t know’ has 2 different meaning. This ‘don’t know’ is represented as NULL in database.
Another question – Mary is one year old. Which school Mary is studying ? – Here answer is ‘Not applicable’ , since Mary is only one year old and not studying anywhere. Here also we represent NULL in database.
NULL is a keyword for almost all relational database system. (Most of the example mentioned below is based on SQL Server.)
Select NULL-2 — NULL
Select NULL+2 –NULL
Select NULL*2 — NULL
Select NULL/2 –NULL
Select NULL/0 –NULL
If we try to concatenate a string with NULL, the result would be NULL.
Create Table Test3 ( dummy Varchar(10))
Insert into Test3 Values (‘AAA’),(‘BBB’),(NULL),(‘CCC’)
Select * from Test3 Where dummy=’AAA’ or NOT (dummy=’AAA’ )
Select * from Test3
See the below statements, we would think that both the statements produce same results because of ‘law of excluded third’ in the laws of classic thoughts, but the fact is that , first statement results 3 rows and second one 4 rows. The reason is that both the OR conditions satisfies to pick the NULL data.
Joining of two tables with columns contain NULL will not consider rows with NULL . That means a NULL will not be able to join with a NULL from another table .The SQL
COALESCE function or
CASE expressions can be used to "simulate" Null equality in join criteria, and the
IS NULL and
IS NOT NULL predicates can be used in the join criteria as well.