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
);

Last updated 28 Jan 2025