Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL for finding rows in parent table without rows in child table

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

From: IANAL_VISTA <IANAL_Vista_at_hotmail.com>
Date: Sat, 09 Apr 2005 02:12:55 GMT
Message-ID: <Xns9632C3782B42SunnySD@68.6.19.6>


"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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US