Why NULL is special

January 30, 2009 11:35 PM

Seen on Twitter today:

Well thank you postgres - ‘where from_date <> thru_date’ ignores records when thru_date is null. I can haz 2 hours back pls?

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.

Previously: I am not Elvis

Next: Birdy Num Num