Parents with no kids

from the Artful Common Queries page


Given tables parent(id INT), child(id INT,parent_id INT), how do we find parents with no children? It's the All X for which there is no Y pattern, which can be written as an exclusion join...


SELECT parent.id

FROM parent 

LEFT JOIN child ON parent.id = child.parent_id 

WHERE child.parent_id IS NULL;



... or with a NOT EXISTS subquery, which is logically equivalent to the exclusion join, but usually performs much more slowly:


SELECT parent.id AS ParentID

FROM parent

WHERE NOT EXISTS (

  SELECT parent.id

  FROM parent 

  JOIN child ON parent.ID = child.parent_id

);




Return to the Artful Common Queries page