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);

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
  • 1
    Can you give us your example? Apr 2 '15 at 8:50
  • yes, and I'll try to rephase the question as well. Apr 2 '15 at 17:31
  • select a.* from a where x not in (select x from b); or: select a.* from a where not exists (select 1 from b where b.x = a.x); Apr 2 '15 at 18:08

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
select child_table.id
from child_table)

where the parent_table.foreign_key_id might match to child_table.id

  • Thanks, this is just what I needed. "parent minus child" was the clue I didn't have! Apr 2 '15 at 17:16
  • oh, and special thanks for figuring out what I needed from my original unclear wording! Apr 2 '15 at 17:32

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.