Oracle: finding rows without children in a table with a foreign key?

Question

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

Answer 1

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

Answer 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.