This post originated from an RSS feed registered with Java Buzz by Charles Miller. Original Post: Why NULL is special Feed Title: The Fishbowl Feed URL: https://fishbowl.pastiche.org/atom.xml Feed Description: tail -f /dev/mind > blog Latest Java Buzz Posts Latest Java Buzz Posts by Charles Miller Latest Posts From The Fishbowl

Seen on Twitter today:

This is one of those things that always seems un-intuitive to developers who aren't familiar with SQL. I've heard a lot of explanations for it over the years, but this is probably the most succinct I've come across, thanks to Matt Ryall on the Atlassian intranet.

…there's actually a reason for this distinction. They're not equivalent at all, because NULL isn't a value that you can be equal to. It represents a value that is undefined.

For example, if my date-of-birth in a database is NULL (because it's unknown) and so it yours, they shouldn't be considered the same. They're two different, undefined values. A query where this might happen is:

select s1.name, s2.name from staff s1, staff s2 where s1.id != s2.id and s1.dob = s2.dob

Or, in the previous example, there's a difference between records where the thru_date is definitely not equal to the from_date, and ones where you just don't know.