CODEWITHSUNDEEP
mysql
user168672
user168672

Reputation:

How does NOT IN subquery work with NULL values?

I am confused how the following works in MySQL. In the queries below, the first SELECT returns all rows from table2 while the second SELECT returns none of the rows. Is there an explanation of how NULL works with the NOT IN operator. Is there any documentation to explains this?

CREATE TABLE table1 (
   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (id)
);

CREATE TABLE table2 (
   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   table1_id INT UNSIGNED,
   PRIMARY KEY (id)
);

INSERT INTO table2 (id, table1_id) VALUES (1, NULL);

SELECT COUNT(*) FROM table2 WHERE table1_id NOT IN (SELECT id FROM table1);
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+

INSERT INTO table1 (id) VALUES (1);

SELECT COUNT(*) FROM table2 WHERE table1_id NOT IN (SELECT id FROM table1);
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

Upvotes: 1

Views: 1685

Answers (2)

Mohamad Hamouday
Mohamad Hamouday

Reputation: 2783

You can add IFNULL(id, '') to sub-query so it will work, example:

SELECT COUNT(*) FROM table2 WHERE table1_id NOT IN (SELECT IFNULL(id, '') FROM table1);

Upvotes: 0

Thomas
Thomas

Reputation: 64655

The reason is that according to the SQL specification, Foo IN(A,B,C) translates to ( Foo = A Or Foo = B Or Foo = C ). Thus, if we have Foo In(Null, 1, 2) we get Foo = Null Or Foo = 1 Or Foo = 2. Since Foo = Null is always UNKNOWN and evaluated to False for purposes of filtering, Nulls in your IN expression will return no results.

Upvotes: 5

Related Questions