Skip to content

Categories:

Using the MySQL IN expression where there are NULL values

If you try to use the MySQL IN or NOT IN expressions in your query where the list of values includes NULL, you will find MySQL ignores the null records.  For example:

Table_A      Table_B
=======      =======
ID           ID
--           --
1            1
2            2
NULL         NULL

SELECT ID 
   FROM Table_A 
   WHERE ID IN (SELECT ID FROM Table_B);

ID
===
1
2

You can see that the null value is missing. This is normal behaviour and complies with SQL standards, but what if you needed null values to appear? You can add an OR condition that includes nulls only if they appear in the destination table. A little inefficient, but it works. It can be achieved like this:

SELECT *
    FROM Table_a
    WHERE ID IN (SELECT ID FROM Table_B)
OR (ID IS NULL AND (SELECT COUNT(*) FROM Table_B WHERE ID IS NULL) > 0);

ID
===
1
2
NULL

Success.

Posted in Uncategorized.