zuluk zuluk -4 years ago 47
MySQL Question

SQL Join with NOT IN() does not work

I have 2 tables that contain both the same key p_id:

test1 test2
+-------------+ +----------------------+
| p_id | name | | o_id | name | p_id |
+-------------+ +----------------------+
| 1 | Paul | | 1 | London | 1 |
| 2 | Marc | | 2 | Paris | 1 |
+-------------+ +----------------------+


Now I want to get all entries from test1 that have no relationship to test2.
In the example above I have abstracted my tables so
RIGHT JOIN
is not possible (in reality I have to join 4 tables).

SELECT a.*,b.*
FROM test1 a
LEFT JOIN test2 b
ON a.p_id=b.p_id
WHERE b.p_id NOT IN(SELECT DISTINCT p_id FROM test2);


I expect one row with
p_id=2
. However I get an empty result.
When I change my code into this:

SELECT a.*,b.*
FROM test1 a
LEFT JOIN test2 b
ON a.p_id=b.p_id
WHERE a.p_id NOT IN(SELECT DISTINCT p_id FROM test2);


Then it works fine. But why? I thought
LEFT JOIN
is processed first (1 row as result) and after that
WHERE
is processed (JOIN has not found
p_id
in
test2
so
b.p_id
is
null
-
null
is not in subselect - so still 1 row as result).

Could someone explain this behavior, please?

Answer Source

It has to do with how NULL is handled in comparisons.

To test/see, you can run simple queries like:

SELECT 1 FROM DUAL WHERE NULL = NULL;

SELECT 1 FROM DUAL WHERE NULL NOT IN (1, 2, 3);

Neither return a row because both conditions return NULL which is "not true".

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download