Query database tables for missing link

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?

Answer

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.