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 🙂 😀

Advertisements

2 thoughts on “Behavior of NULL values in NOT IN clause – MYSQL

  1. Good observation……
    But this not the design issue of IN Cause….Its for all for example
    select * from Persons where favorite_color!=’Blue’;
    you get the same output as you get from NOT IN cause

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s