Oracle: finding rows without children in a table with a foreign key? 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
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.