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

Davy wrote:
> 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.

I believe you intended to state you can use an INNER join to find the countries having a city. You can use an outer join to find the countries which do NOT have a city, which is precisely the reason outer joins exist, to find unmatched records:

SQL> create table country (countryid number, name varchar2(40));

Table created.

SQL> create table cities (countryid number, city_name varchar2(40));

Table created.

SQL> insert into country values (1, 'Blinkenstein');

1 row created.

SQL> insert into country values(2, 'Yankenstein');

1 row created.

SQL> insert into country values (3, 'Flurbenstein');

1 row created.

SQL> insert into country values (4, 'Eubangostein');

1 row created.

SQL> insert into country values (5, 'Quackenstein');

1 row created.

SQL> insert into cities values (1, 'Yerble');

1 row created.

SQL> insert into cities values (1, 'Squink') SQL> / 1 row created.

SQL> insert into cities values (1, 'Plotzen') SQL> / 1 row created.

SQL> insert into cities values (2, 'Yarbo');

1 row created.

SQL> insert into cities values (2, 'Eng');

1 row created.

SQL> insert into cities values (4, 'Quagmire');

1 row created.

SQL> commit;

Commit complete.

SQL> select m.countryid, m.name, c.countryid city_country
  2> from country m, cities c
  3> where c.countryid (+) = m.countryid
  4> and c.countryid is null;

 COUNTRYID NAME                                     CITY_COUNTRY
---------- ---------------------------------------- ------------
         3 Flurbenstein
         5 Quackenstein

David Fitzjarrell Received on Fri Apr 08 2005 - 13:44:21 CDT