The null pitfall in SQL

20 Feb 2018

Today, I've encountered a counter-intuitive behaviour around null in Postgresql.

SELECT * FROM users WHERE facebook_id != 123 will not match any row whose facebook_id is null.

Why? What? I mean null isn't equal to 123. Why would we design SQL to work this way?

I bet the reason must be performance related. It always is.

From now on, when working with null, especially a negate condition, we need to remember to add IS NOT NULL.

Edit: from the reddit thread, we should use IS DISTINCT FROM.