NULL


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

omega greekEven if we try to divide NULL with a Zero , it will not return division by zero exception in most of the RDBMS, This is not an ISO defined behaviour but most of the RDBMS systems follow this.

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.

Advertisements

Comment please...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s