The SQL NULL paradox

Level: 1 where 1 is noob and 5 is totally awesome
System: MS SQL Server or PostgreSQL

The Paradox 

One of the best things about computing, it is so logical. We can always trust in 1 + 1 = 2, 1 < 2 or 1 = 1. So now I'm going to throw 4 queries and their results, as an example:

SELECT CASE WHEN 42 > 42 THEN 'True' ELSE 'False' END; -- Result: False

SELECT CASE WHEN 42 < 42 THEN 'True' ELSE 'False' END; -- Result: False

SELECT CASE WHEN 42 != 42 THEN 'True' ELSE 'False' END;-- Result: False

SELECT CASE WHEN 42 = 42 THEN 'True' ELSE 'False' END; -- Result: True

Hopefully no surprise there. Now I'm going to throw 4 queries more, and their result:

SELECT CASE WHEN NULL > NULL THEN 'True' ELSE 'False' END; -- Result: False

SELECT CASE WHEN NULL < NULL THEN 'True' ELSE 'False' END; -- Result: False

SELECT CASE WHEN NULL != NULL THEN 'True' ELSE 'False' END;-- Result: False

SELECT CASE WHEN NULL = NULL THEN 'True' ELSE 'False' END; -- Result: False

The three first lines, seems logical, but wait a minute:-). How is this possible? If NULL != NULL then NULL = NULL  must be true, else it is illogical. Or is it?

The Explanation

Actually it is not paradox. The reason, some might expect NULL = NULL to be true, is because they misunderstand NULL. They mistake NULL for being a value. If NULL was a value, then NULL = NULL would be true. So if it is not a value, what is it then?

The ANSI SQL-92 Specification says about NULL:

"null value (null): A special value, or mark, that is used to indicate the absence of any data value."   

I can understand the confusion, because have just written, it is not a value. But you have to notice it says "A special value". Absence of data value, is the right way to see NULL,  unknown or missing.

To be able to handle NULL values defined in the SQL-92 specs. Databases servers such as SQL Server and PostgreSQL uses trinary logic and not binary logic. It means, logic operations can have 3 out comes: True, False or NULL.

Because NULL is considered to be unknown, any comparison to NULL, even NULL vs. NULL will have False as result, because it is unknown what we compares. While using any arithmetic operator with NULL, will have NULL as result. The latter makes good sense. Lets say you add something to unknown, then result is unknown.

So in the end, it's all logical. For more read Handling Null Values for SQL Server, or 9.2. Comparison Operators for PostgreSQL. Even better read them both:-)

T-SQL: Comma Separating Values into Fields

Level: 2 where 1 is noob and 5 is totally awesome
System: SQL Server from 2005 and up

Visualising data

When querying data from databases, I'm always trying to get resultsets which are most presentable as possible. It save me a lot of time, because I then don't need to transform data further. The upsides are, I can either save a resultset as csv or just copy paste it to some spreadsheet or to a mail, right after the querying. The down side is, I have to type a bit more SQL.

In this blog post, I'll present one of my favorites methods, to easily visualize data in a smart way. I'm going to explain the method, using these 2 tables:

Items:
Id          Name                                               ColorId
----------- -------------------------------------------------- -----------
1           Cranberries                                        1
2           Plants                                             2
3           Shrimps                                            1
4           Ocean                                              3
5           Sky                                                3
6           Roses                                              1  

And

Colors:
Id          Color
----------- --------------------------------------------------
1           Red
2           Green
3           Blue

The most used way to show, the color for each item would be to join the tables like:

 SELECT Items.Id, Items.Name, Color FROM Items  
 INNER JOIN Colors ON Items.ColorId = Colors.Id  

Which gives:

Name                                               Color
-------------------------------------------------- -----------------
Cranberries                                        Red
Plants                                             Green
Shrimps                                            Red
Ocean                                              Blue
Sky                                                Blue
Roses                                              Red

It is readable, and if we sort by color it might be more readable. It might be most readable, if we could group the items by color. It requires a bit more SQL; but the result is better:

 SELECT   
  STUFF(  
      (SELECT ', ' + Name  
      FROM Items  
      where Items.ColorId = Colors.Id  
      FOR XML PATH (''))  
      , 1, 1, '') AS Items,  
 Color  
 FROM Colors;  

Gives:

Items                                Color
------------------------------------ ------------------------------------------
Cranberries, Shrimps, Roses          Red
Plants                               Green
Ocean, Sky                           Blue

Explanation

The magic happens in the inner SQL. FOR XML PATH is used as a smart way for concatenating string columns to one string column. By adding the ',' to Name, we are getting a comma separated list, where the first char is ','. For more about FOR XML PATH see: http://technet.microsoft.com/en-us/library/ms189885.aspx

STUFF are used to remove the first char(in this case ','), from the comma separated lists. Stuff is a sql command for inserting strings into strings, but is can also remove chars. In this case we are overwriting first char with a empty string. For more about STUFF see: http://technet.microsoft.com/en-us/library/ms188043.aspx

The rest should be self explanatory :-)