In this article, I want to show you queries might sometimes return results which may look strange at first glance. You need to remember the devil is in the detail while creating queries 😈

I've prepared a simple database table for tests. It contains a few products:

I want to fetch all rows except rows where discount_id equals 2.

SELECT * FROM products WHERE discount_id NOT IN (2);

The result is:

Wait... Where is Geforce RTX 4080 which contains discount_id equals NULL?!

It's the issue which I have wanted to show you :-)

What's going on?

This behavior might be weird for software developers who code in JavaScript or PHP. For these languages, NULL is treated like false. In SQL, NULL means something is unknown - the information is missed. Unknown doesn't equal false!

Have a look on these examples:

SELECT 'true' WHERE 3 <> NULL; # no results
SELECT 'true' WHERE NOT (3 <> NULL); # no results
SELECT 'true' WHERE 3 IS NOT NULL; # true

You have to remember NULL is a special state. You need to think about it if you use columns with NULL values in your conditions.

It's time to fix my query. If I want to fetch all rows expect discount_id equals 2, I just need to add one more condition:

SELECT * FROM products WHERE discount_id NOT IN (2) OR discount_id is NULL;

That's it! I hope my post will help you to avoid this annoying issue.