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? – Colin 't Hart Apr 2 '15 at 8:50
  • yes, and I'll try to rephase the question as well. – Mark Harrison 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); – ypercubeᵀᴹ 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

| improve this answer | |
  • Thanks, this is just what I needed. "parent minus child" was the clue I didn't have! – Mark Harrison Apr 2 '15 at 17:16
  • oh, and special thanks for figuring out what I needed from my original unclear wording! – Mark Harrison 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.

| improve this answer | |

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.