Re: Postgresql sql query - selecting rows outside a join

A suggestion is to use a left outer join and then test for rows with a
right-hand null value (ones where the join failed).

The following example creates 2 tables, t1 and t2.
t1 has related records in t2 and the relation is indicated by
t1.rel->t2.id

create table t1 (
id integer,
rel integer,
label varchar(10)
);

create table t2 (
id integer,
label varchar(10)
);

insert into t1 (id, rel, label) values (1, 1, 'bob');
insert into t1 (id, rel, label) values (2, 2, 'sam');
insert into t1 (id, rel, label) values (3, 0, 'alice');

insert into t2 (id, label) values (1, 'martin');
insert into t2 (id, label) values (2, 'gwen');

-- this shows you all the records
select t1.*, t2.* from t1 left outer join t2 on (t1.rel = t2.id);

-- this shows you those where the inner join fails (your where X NOT IN
(y) stuff)
-- the result should be where t1.id=3 because it has a t1.rel of 0
therefore no related
-- record in t2
select t1.*, t2.* from t1 left outer join t2 on (t1.rel = t2.id) where
t2.id is null

Toby
-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Graham Leggett
Sent: 01 December 2003 17:16
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Postgresql sql query - selecting rows outside a join

Hi all,

I am trying to do a query that returns all rows that are _not_ part of
a join, and so far I cannot seem to find a query that doesn't take 30
minutes or more to run.

The basic query is "select * from tableA where tableA_id NOT IN (select
tableA_id from tableB)".

Is there a more efficient way of doing this?

Regards,
Graham
--

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html