Behavior of NULLS

One of the more interesting aspects of database management systems is the concept of NULL markers. Because they are a source of great confusion and much debate (for more on this debate, I highly recommend C.J. Dates’ Database in Depth: Relational Theory for Practitioners) I decided to create a summary of all the different SQL constructs and how they treat the NULL markers.

Link to my GitHub document here: Behavior of NULLS

Because NULL markers are treated with varying logic throughout all the different SQL constructs, I try and make a game out of finding all the different ways their behavior affects the SQL language and update the my document as such.

For this update, I included some nuances with how NULLs are treated in stored procedure return types, views, ascii functions and the bit data type.

I find the bit data type to be the best example that encapsulates the behavior of the NULL marker. Often we think of a bit data type as a Boolean value that is either true or false, yes or no, on or off, 1 or 0, etc… however the SQL bit data type is not a true Boolean value as it accepts both 1,0 and NULL. Because NULL is treated as an unknown, we know that for a bit data type NOT(1) will only include 0 and NOT(0) will only include 1, and NULL markers will not be returned using this predicate logic.

Even if you are an advanced database developer, I highly recommend going through this document and fill in any necessary cracks you may have in your knowledge of NULL markers. And if I’ve missed anything, please let me know as well and I would love to include them in my writing.

Happy coding!

Leave a Reply