2

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.

Improve this question
2

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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.
2

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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.