CODEWITHSUNDEEP
mysqlsql
Leon
Leon

Reputation: 429

sql:using not in and select nothing

table 'tree':

+----+------+
| id | p_id |
+----+------+
| 1  | null |
| 2  | 1    |
| 3  | 1    |
| 4  | 2    |
| 5  | 2    |
+----+------+

and these codes select nothing out.why?

select id,'Leaf' as type
from tree
where  id not in (select distinct p_id from tree)

while these is working right,using two times for a same condition

SELECT
    id, 'Leaf' AS Type
FROM
    tree
WHERE
    id NOT IN (SELECT DISTINCT
            p_id
        FROM
            tree
        WHERE
            p_id IS NOT NULL)
        AND p_id IS NOT NULL

Upvotes: 2

Views: 493

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271141

Don't use NOT IN with a subquery. As Jarlh says in a comment, when any value is NULL, then no rows are returned at all.

Instead, get used to NOT EXISTS:

select t.id, 'Leaf' as type
from tree t
where not exists (select 1 from tree t2 where t2.p_id = t.id);

This behaves as you would expect.

Although you could fix the problem using WHERE t2.p_id IS NOT NULL in the subquery, you might as well use NOT EXISTS. At some future point, you'll find yourself debugging another NOT IN where you left out the WHERE clause.

Upvotes: 4

Related Questions