Okay it is not Dr. Jekyll and Mr. Hyde but I had to come up with a title. This was a question someone asked yesterday in the sql programming group http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/126735827b7ae667# This person had a case statement like this SELECT CASE WHEN COL1 IS NULL THEN 'UNK' ...

Thinking more about null, I seems to me that the primary problem with nulls is that the theoretical definition of null is very different than the pragmatic use of null.   We all learned in DB101 that Null means ''unknown'' and this is why Null = Null is false.   In practice however, there’s nothing unknown about Null at all. ...

This is a frequent request in newsgroups and fora. People want to sort the column in ascending order but don't want the NULLS at the beginning. Oracle has this syntax: ORDER BY ColumnName NULLS LAST;SQL Server does not have this. But there are 2 ways to do this. The first one is by using case and the second one by using COALESCE and the maximum ...

In my previous post on NULLs I discussed (rather replied to Hugo Kornelis) some more academic concepts and definitions when it comes to NULL. It is very sad for me to admit that, while interesting to me and many others, that sort of discussion is all but useless the instant you walk into the practical world. Near the end of my last post, I ...

In this fourth and final part in my series about NULL, I’ll discuss some well-known and some less well-known functions and keywords that are specifically created to deal with NULL values. And I will, of course, explain why null if null is null null null is null. In case you have not yet read them, you can click these links to read the first, ...

Two months ago, I posted the first two parts of a series about NULL. After that, I went quiet. Much to do and little time was one excuse. But to be honest, I also lost interest. However, I felt I owe my readers to conclude the series, so I have now forced myself to write and publish the last parts before moving on to more interesting subjects ...

While still working on a post on the misuse of NULLs, I stumbled across the book ''Applied Mathematics for Database Professionals'' by Lex de Haan and Toon Koppelaars. Having some background in mathematics, set theory and a lot of background in logic, I found this book to be a gem. Not a polished gem that you might put on a neckless to give to ...

In my previous post, I explained what NULL does and does not mean, how and why the rule of NULL propagation forces any expression involving NULL to result in NULL. I also mentioned that comparisons involving NULL do not result in NULL but, rather, in a new “truth value” called “Unknown”. But there’s a lot more to be written about Unknown and it’s ...

Strange title, yes. After reading Hugo Kornelis' excellent post on NULLs, I found myself getting very little sleep as I pondered the points he made. If you haven't already read it, do so, now prefereably. The two main points he brought out were (correct me if I'm wrong Hugo): 1) Unknown (among others) is not the best working academic ...

I once read a scientific article on black holes that started of on the observation that, since a hole is the absence of anything and black is invisible in space, a black hole is in fact an invisible nothing – so what the heck are we talking about? Well, almost the same can be said about NULL in databases.   Let’s first take a look at what ...