Select records whose id does not appear in multiple tables

Question

I have 3 tables. Two tables (par1, par1) both refer (child_id) to an id in the 3rd table (child).

I like to find orphans in the 3rd table, i.e. records that are not referenced by either of the other two tables.

If I had only once referencing table, I could write:

SELECT * FROM child WHERE id NOT IN (SELECT child_id FROM par1)

But how do I solve this for two referencing tables?

I'm using sqlite.

score 5 · Accepted Answer · edited May 28 '13 at 23:21

5

SELECT * 
FROM child 
WHERE id NOT IN (SELECT child_id FROM par1) AND 
      id NOT IN (SELECT child_id FROM par2)

edited May 28 '13 at 23:21

JohnFx

34,542
18
104
162

answered May 28 '13 at 23:21

lightbricko

2,649
15
21

Ah, haha! I had thought about joined selects and other complicated things, but this is so simple, I am a bit embarrassed now :) – Thomas Tempelmann May 28 '13 at 23:22
Wow, you're already a year on SO, and only now getting your first "best answer". Now I'm not feeling so bad for asking this question any more. Cheers! :) – Thomas Tempelmann May 28 '13 at 23:34
1

Well... I had to answer some questions today to be able to post a question with embedded images: http://stackoverflow.com/questions/16804510/how-to-avoid-data-loss-with-ef-model-first-database-schema-upgrade – lightbricko May 29 '13 at 02:29

score 1 · Answer 2 · answered Nov 20 '15 at 23:15

1

An alternative is to use LEFT OUTER JOIN:

SELECT child.*
FROM child LEFT OUTER JOIN par1 ON (child.id = par1.child_id)
           LEFT OUTER JOIN par2 ON (child.id = par2.child_id)
WHERE par1.child_id IS NULL AND par2.child_id IS NULL

Executing subqueries may or may not find the desired optimization paths in the SQLite index decision engine.

answered Nov 20 '15 at 23:15

CubicleSoft

2,274
24
20

Thank you! Exactly what I was looking for. – Tom Davenport Apr 05 '21 at 21:16

Select records whose id does not appear in multiple tables

2 Answers2