Yesterday I fell into a very simple but dangerous SQL trap. Can you identify it?
SELECT first_column, another_column FROM my_table WHERE first_column = 1 AND (another_column >= 100 OR another_column = null)
Found it? It’s the comparison to null. My intention was to get rows where another_column was null, however the query did not do so. It didn’t give me any results where another_column was null, but didn’t give me any error either. Why?
Null in SQL is not a real value. Null means “lack of a value” or “unknown”. Anything compared to null leads to an “unknown” result. Therefore, in SQL there are actually three values conditions can evaluate to: true, false, and unknown/null. This is called three-valued logic. SQL WHERE clauses only return rows that evaluate to true, not rows that evaluate to false or “unknown”.
The proper way of checking for null is by using the IS NULL or IS NOT NULL predicates. The correct SQL query would have been as follows:
SELECT first_column, another_column FROM my_table WHERE first_column = 1 AND (another_column >= 100 OR another_column IS null)
So if you’re used to languages where you check for null with equal signs, remember that in SQL, you must use the special IS NULL or IS NOT NULL predicates instead of the equals sign or other comparison operators. Don’t get tripped up like I did.
If you want to read more about, Wikipedia has a detailed article on null in SQL.
