0

I'm writing code to hit a tree structure which is persisted in a MySQL database, each node storing the ID of its parent or NULL if it's the root of the tree. In the course of querying it to try to get all leaf nodes, I noticed something odd.

Namely, this query produces no results:

SELECT * 
FROM tree_table 
WHERE node_id NOT IN(SELECT parent_node_id FROM tree_table)

while this one produces the results I'm after:

SELECT * 
FROM tree_table
WHERE node_id NOT IN(
    SELECT node_id 
    FROM tree_table 
    WHERE node_id IN(SELECT parent_node_id FROM tree_table))

It seems to be the NOT that's giving me the trouble. Is this something about order of operations or similar that I'm misremembering?

Glazius
  • 729
  • 7
  • 28

1 Answers1

4

If 

SELECT parent_node_id FROM tree_table

returns a single NULL amongst it's result set, then the rest of the query 

SELECT *   
FROM tree_table   
WHERE node_id NOT IN(SELECT parent_node_id FROM tree_table)

will produce no results.

See NOT IN clause and NULL values (one of many related questions)

Community
  • 1
  • 1
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541