Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL for finding rows in parent table without rows in child table
"Davy" <daveellison2000_at_gmail.com> wrote in
news:1112982181.080024.313640_at_z14g2000cwz.googlegroups.com:
> Hi,
> I have 2 tables - Countries and Cities for example, and the Cities
> table has a foreign key to the Countries table. How can I find all the
> Countries without a City in the Cities table?
>
> My initial attempt using a subquery like:
>
> select distinct country_id from countries where country_id NOT IN
> (select distinct country_id from Cities)
>
> Won't work because my tables are huge (over 1 million rows each) and
> the query dosn't come close to finishing with so many records in the
> NOT IN statement.
>
> I know I can use an outer join (+) to find all the Countries that DO
> have a city, but not the other way around. This seems like it shouldn't
> be that hard but I'm pretty stumped.
>
> Thanks for any suggestions.
>
select distinct country_id from countries
MINUS
select distinct country_id from Cities;
Received on Fri Apr 08 2005 - 21:12:55 CDT