MySQL 'NOT IN' returns empty result even when there is no match

Question

My table is:

Tree table:
+----+------+
| id | p_id |
+----+------+
| 1  | null |
| 2  | 1    |
| 3  | 1    |
| 4  | 2    |
| 5  | 2    |
+----+------+

If I execute

SELECT id FROM Tree
WHERE id IN (SELECT p_id FROM Tree)

I get the expected answer

+----+
| id | 
+----+
| 1  | 
| 2  | 
+----+

However, if I execute

SELECT id FROM Tree
WHERE id NOT IN (SELECT p_id FROM Tree)

I get an empty result:

+----+
| id | 
+----+ 
+----+

instead of the expected 3, 4, 5. As far as I understand, NOT IN should give the complementary set given by IN (excluding NULLs)?

New to SQL, sorry if I'm missing something basic.

It's working correctly. There are a null/missing values in the table. — The Impaler, Aug 23 '22 at 15:03
https://stackoverflow.com/questions/10810391/why-mysql-in-keyword-not-considering-null-values — Esther, Aug 23 '22 at 15:06

The Impaler · Answer 1 · 2022-08-23T15:40:09.483

4

Nulls represent missing but existing data. Therefore the engine can't really decide if a row is not in the set when it doesn't find a clear match. As a result NOT IN will produce no rows in the presence of nulls.

Anyway, you can use an anti-join to get the result you want. For example:

select a.id
from tree a
left join tree b on b.p_id = a.id
where b.p_id is null

Alternatively, you can use NOT EXISTS.

edited Aug 23 '22 at 15:40

answered Aug 23 '22 at 15:06

The Impaler

45,731
9
39
76

1

The engine can decide if a value is in the list, but it cannot decide if something is not in the list if the list contains a null (because something = null or something = something is decidably true, while something = null or something = something_else is not decidable and thus unknown (null), and not (unknown) is unknown. – Mark Rotteveel Aug 23 '22 at 15:11

score 2 · Answer 2 · answered Aug 23 '22 at 15:07

In your case you could use NOT EXIST which takes in consideration NULLS, while IN operator doesn't

SELECT id FROM Tree
WHERE  NOT exists (SELECT 1 FROM Tree t1 where t1.p_id=Tree.id) ;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=fff39bf4a29193a8eb9644b9c126771b

MySQL 'NOT IN' returns empty result even when there is no match

2 Answers2