Behavior of NULL values in NOT IN clause – MYSQL

Hi,

This post will be discussing the Behavior of NULL values in NOT IN clause – MYSQL.

Assume following table schema:

mysql> select * from Persons;

+—-+——+—————-+

| id | name | favorite_color |

+—-+——+—————-+

|  1 | ABC  | Blue           |

|  2 | DEF  | NULL           |

|  3 | XYZ  | Yellow         |

|  4 | PQR  | Blue           |

+—-+——+—————-+

If you want to select all Persons whose favorite_color is Blue. The ‘IN’ clause will do the needful.

mysql> select * from Persons where favorite_color IN (“Blue”);

+—-+——+—————-+

| id | name | favorite_color |

+—-+——+—————-+

|  1 | ABC  | Blue           |

|  4 | PQR  | Blue           |

+—-+——+—————-+

 

Now, comes the crux. If you want to select all the Persons whose favorite_color is NOT Blue, you would use the ‘NOT IN’ clause. And you would expect to get 2 records, viz., Persons having name ‘DEF’ and ‘XYZ’, having favorite_color as ‘NULL’ and ‘Yellow’ respectively.

Let’s do the same.

mysql> select * from Persons where favorite_color NOT IN (“Blue”);

+—-+——+—————-+

| id | name | favorite_color |

+—-+——+—————-+

|  3 | XYZ  | Yellow         |

+—-+——+—————-+

Oh! That’s not what you expected!

That’s how the MYSQL NOT IN clause is designed to treat NULL values. So, if you get in such situations, you should add ‘OR IS NULL’ clause too.

mysql> select * from Persons where (favorite_color NOT IN (“Blue”) OR favorite_color IS NULL);

+—-+——+—————-+

| id | name | favorite_color |

+—-+——+—————-+

|  2 | DEF  | NULL           |

|  3 | XYZ  | Yellow         |

+—-+——+—————-+

Yippee, that’s what we wanted!

Point to remember, else you might end up losing some records (data).

Cheers,

Aishwarya 🙂 😀