Query database tables for missing link

Question

I have two tables in my database and they each share the a field called cID. So here's an example:

Parent Table
_______________
cID  Name
--   ------
1    Record #1
2    Record #2
3    Record #3


Child Table
_______________
ID   cID   Name
--   ---   -----
10    1    Record #1
11    1    Record #2
12    2    Record #3
13    1    Record #4

So what's happened is that someone has gone in and deleted cIDs from the parent the parent table and the child table still has those cIDs but they now reference nothing. Is there a SQL statement that I could use to select all the records in the child table that no longer have a cID in the parent table?

Read up on JOINs http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-join/16598900#16598900 — Raj More, Apr 17 '14 at 17:57

crthompson · Accepted Answer · 2014-04-17T18:33:39.597

1

In your text, you say that parents are missing, but your example shows a parent without a child.

For parents without children in a left join:

select
  p.*
from
  parent p
  left join child c on p.cId = c.cId
where
  c.cid is null

I created a SQL Fiddle as an example.

For children without parents:

select
  c.*
from
  child c
  left join parent p on p.cId = c.cId
where
  p.cid is null

Fiddle

Note that if you changed the first query to a RIGHT join, you'd get the same answer as the second query where I've changed the sequence of the tables.

edited Apr 17 '14 at 18:33

answered Apr 17 '14 at 17:49

crthompson

15,653
6
58
80

For some reason, i ALWAYS use INNER JOIN. What does left join do? – Damien Apr 17 '14 at 17:56
@Damien Left, right, full.. all different kinds of joins that give you different stuff. :) [Check it out](http://stackoverflow.com/q/13997365/2589202). That image is a great way to view your joins. – crthompson Apr 17 '14 at 17:58

score 0 · Answer 2 · answered Apr 17 '14 at 17:55

0

With Subselect:

SELECT * FROM Child 
WHERE cID NOT IN (SELECT cID FROM Parent)

With Join:

SELECT Child.*
FROM Child
LEFT JOIN Parent ON Child.cID = Parent.cID
WHERE Parent.cID IS NULL

answered Apr 17 '14 at 17:55

Tennyson Hull

43
2
5

score 0 · Answer 3 · answered Apr 17 '14 at 17:55

0

Here is an alternate solution

Select Child.cId
From Child
Where Not Exists
(
Select 1
From Parent
Where Child.cID = Parent.cID)
)

You can do the same thing with an IN, but EXISTS with a JOIN is a much more efficient solution.

answered Apr 17 '14 at 17:55

Raj More

47,048
33
131
198

Beat me to the punch. – beercodebeer Apr 17 '14 at 17:56

score 0 · Answer 4 · answered Apr 17 '14 at 17:55

Along with the left join to find nulls, you could also use where not exists:

select *
from Child
where not exists (
    select 1
    from Parent
    where cID = Child.cID
)

Depending on number of records and other criteria of your data model, one may be more performant than the other.

Query database tables for missing link

4 Answers4