PL/SQL for finding rows in parent table without rows in child table

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. Received on Fri Apr 08 2005 - 12:43:01 CDT