1

Suppose I have two tables with a foreign key relationship. How can I find the rows in the "parent" table for which there is no corresponding "child" row?

For example:

create table a(x number primary key);
create table b(x number);
alter table b add constraint b_fk foreign key(x) references a(x) enable;

insert into a values(1);
insert into b values(1);
insert into a values(2);
commit;

delete from a where x = 2; -- will succeed
delete from a where x = 1; -- will fail, because there is a child row.

select ??? as "rows with no children" from a;

rows with no children
---------------------
                    2
MDCCL
  • 8,530
  • 3
  • 32
  • 63
Mark Harrison
  • 829
  • 5
  • 20
  • 33

2 Answers2

4

A semantically appropriate way of expressing this would be:

select ...
from   a
where  not exists (
         select null
         from   b
         where  b.x = a.x)

NOT EXISTS uses an anti-semi-join which allows the optimiser to be very responsive to the table and join cardinalities.

David Aldridge
  • 2,198
  • 11
  • 14
3

Given the lack of sample tables I have to make this up....

select * from parent_table
where parent_table.foreign_key_id in
(select parent_table.foreign_key_id
from parent_table
minus
select child_table.id
from child_table)

where the parent_table.foreign_key_id might match to child_table.id

kevinskio
  • 4,272
  • 1
  • 30
  • 50