NULL

For relational databases, the NULL is the unknown or it can simply mean that the value is absent. Just like in real world you can't add something to an unknown quantity and make it a known one in the relational world. So NULL is like the evil queen from fairy tales that turns every known value to unknown if not properly handled. Lets see

SELECT 'binyoga' + NULL;
GO 

Results a NULL. It seems this is the reason that some developers don't like allowing NULLs in their databases. Anyways, we all know that an unknown is not equal to another unknown, right? Even if it is, it is still unknown. SQL Server knows that too.

IF NULL = NULL
 SELECT 'Whoa, I cracked the unknown'
ELSE
 SELECT 'Nope. Unknown is not equal to unknown :(' 

So how do you deal with this? Enter IS NULL.

IF NULL IS NULL
 SELECT 'Yes. It is NULL. You don''t need my help to figure'
ELSE
 SELECT 'No, the world is ending. NULL is NOT NULL anymore'

You can use this guy to find out all the people that doesn't have Mr/Mrs titles in AdventureWorks database.

SELECT LastName + ',' + FirstName AS Name
FROM Person.Person
WHERE Title IS NULL
-- I wonder why 18K people have no titles!

Okay, so you push data to reports people and they don't like NULLs. How do you handle that? Enter ISNULL() function. More about that later. Trivial and basic stuff - yes. But blogging about it is a way to think again about it. Where did I get this info from? Like I said, I have been reading SQL Server Bible by Paul Nielsen and my next few posts are likely to be influenced by the book. Its an aswesome book, btw!