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.