SELECT When child record doesn't exist

Question

I have two tables parent and children. The parent.mopid and children.mopid is the connection between the two tables. How would I write a SELECT that would end result show me just the parent records where there are no children records?

score 3 · Answer 1 · answered Jun 14 '13 at 17:31

3

Use the NOT IN function:

SELECT * from parent
where parent.mopid NOT IN (SELECT mopid from children)

This will return all rows from the parent table that do not have a corresponding mopid in the childrens table.

answered Jun 14 '13 at 17:31

Kris Gruttemeyer

872
7
19

Ed Gibbs · Answer 2 · 2013-06-14T18:01:34.170

3

If you have a lot of rows, a LEFT JOIN is often quicker than a NOT IN. But not always - it depends on the data so please try this answer and the one from @aktrazer and see which works best for you.

SELECT parent.*
FROM parent
LEFT JOIN children ON parent.mopid = children.mopid
WHERE children.mopid IS NULL

If there isn't a children row for the mopid, parent.mopid will have a value but child.mopid will be null.

edited Jun 14 '13 at 18:01

answered Jun 14 '13 at 17:35

Ed Gibbs

25,924
4
46
69

Not necessarily as explained in this article, depends on the data and other factors: http://blog.sqlauthority.com/2008/04/22/sql-server-better-performance-left-join-or-not-in/ – Kris Gruttemeyer Jun 14 '13 at 17:39
You're right @aktrazer, and sorry - I shouldn't make a sweeping statement like that. I'll alter my answer to make it less strident, and I'll up-vote your answer because (a) it works and (b) the OP should try both solutions and determine which is faster based on their data. – Ed Gibbs Jun 14 '13 at 17:59
Not a problem, we're all here to learn and help out. Your answer was 100% right, OP should just try both and see which one works better for him. – Kris Gruttemeyer Jun 14 '13 at 18:01
You're too kind @aktrazer - as you continue with SO you'll see soon enough that some folks will downvote without explanation rather than state their case candidly. I much prefer your approach :) – Ed Gibbs Jun 14 '13 at 18:04

score 3 · Answer 3 · edited May 23 '17 at 12:31

3

SELECT * from parent p where NOT EXISTS 
( select mopid from children c where p.mopid = c.mopid)

This should take care of the nulls as well

This link will explain you the difference between NOT IN and NOT EXISTS

NOT IN vs NOT EXISTS

edited May 23 '17 at 12:31

Community

1
1

answered Jun 14 '13 at 17:39

Jasti

927
5
14

Well played. Learn something new every day. – Kris Gruttemeyer Jun 14 '13 at 17:56

SELECT When child record doesn't exist

3 Answers3