find the "missing" rows

From: "Kevin B(dot)" <db(at)ke5in(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: find the "missing" rows
Date: 2004-12-01 15:43:30
Message-ID: 33080.67.87.27.161.1101915810.squirrel@www.ke5in.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I would like to find the "missing" rows between two sets without using a
subselect (or views).

This query finds the rows that are in t1 but not in t2. (see the script
below for table definitions.)

QUERY 1:
--------
select * from t1 left join t2 on t1.i = t2.i where t2.i is null

The above query is across two tables. I'm having trouble when all the
data is in the same table. Here is my attempt but it does not work. I've
thought about doing it with views but I really would like to try without
views or a subselect...

QUERY 2:
--------
Select a.i, b.i
from t as a
left join t as b on a.i = b.i
where a.n = 'a' and b.n = 'b' and b.i is null

Is there some clever trick get a query working in similar fashion to QUERY
1 but when all the data is in the same table (as in table "t")?

Definitions for Query 1
------------------------
create table t1 (i int);
insert into t1 values(1);
insert into t1 values(2);
insert into t1 values(3);
insert into t1 values(4);
insert into t1 values(5);

create table t2 (i int);
insert into t2 values(1);
insert into t2 values(2);
insert into t2 values(3);
insert into t2 values(5);

Definitions for Query 2
------------------------
create table t (n varchar(10), i int);
insert into t values('a',1);
insert into t values('a',2);
insert into t values('a',3);
insert into t values('a',4);
insert into t values('a',5);
insert into t values('b',1);
insert into t values('b',2);
insert into t values('b',3);
insert into t values('b',5);

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Michael Fuhr 2004-12-01 17:53:01 Re: inserting values into types
Previous Message Tom Lane 2004-12-01 15:42:49 Re: SET AUTOCOMMIT TO OFF