Обсуждение: find the "missing" rows

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);

"Kevin B." <db@ke5in.com> writes:
> 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

This can't succeed since the b.n = 'b' condition is guaranteed to fail
when b.* is nulled out ...
        regards, tom lane

On 2004-12-02, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin B." <db@ke5in.com> writes:
>> 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
>
> This can't succeed since the b.n = 'b' condition is guaranteed to fail
> when b.* is nulled out ...

You can make it work by moving parts of the condition into the explicit
join clause:

select a.i from t as a left join t as b on a.n='a' and b.n='b' and a.i=b.i where a.n='a' and b.i is null; 

(notice you still need the check on a.n='a' outside the join condition)

-- 
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services