Обсуждение: self join
Hi, This probably reflects my confusion with how self joins work. Suppose we have this table: =# SELECT * FROM tmp;a | b ---+---1 | 22 | 34 | 5 (3 rows) If I want to get a table with records where none of the values in column b are found in column a, I thought this should do it: =# SELECT * FROM tmp t1, tmp t2 WHERE t2.b <> t1.a;a | b | a | b ---+---+---+---1 | 2 | 1 | 21 | 2 | 2 | 31 | 2 | 4 | 52 | 3 | 2 | 32 | 3 | 4 | 54 | 5 | 1 | 24 | 5 | 2 | 34 | 5 | 4 | 5 (8 rows) I need to get: a | b | a | b ---+---+---+---1 | 2 | 1 | 24 | 5 | 4 | 5 Or just: a | b ---+---1 | 2 4 | 5 -- Seb
Hi 2011/5/15 Seb <spluque@gmail.com>: > Hi, > > This probably reflects my confusion with how self joins work. > > Suppose we have this table: > > =# SELECT * FROM tmp; > a | b > ---+--- > 1 | 2 > 2 | 3 > 4 | 5 > (3 rows) > > If I want to get a table with records where none of the values in column > b are found in column a, I thought this should do it: > > =# SELECT * FROM tmp t1, tmp t2 WHERE t2.b <> t1.a; > a | b | a | b > ---+---+---+--- > 1 | 2 | 1 | 2 > 1 | 2 | 2 | 3 > 1 | 2 | 4 | 5 > 2 | 3 | 2 | 3 > 2 | 3 | 4 | 5 > 4 | 5 | 1 | 2 > 4 | 5 | 2 | 3 > 4 | 5 | 4 | 5 > (8 rows) > > I need to get: > > a | b | a | b > ---+---+---+--- > 1 | 2 | 1 | 2 > 4 | 5 | 4 | 5 > > Or just: > > a | b > ---+--- > 1 | 2 > 4 | 5 Your query doesn't have an explicit join and is producing a cartesian result. I don't think a self- join will work here; a subquery should produce the result you're after: SELECT * FROM tmp t1WHERE NOT EXISTS(SELECT TRUE FROM tmp t2 WHERE t2.b=t1.a); HTH Ian Lawrence Barwick
On Sun, 15 May 2011 07:39:06 +0900, Ian Lawrence Barwick <barwick@gmail.com> wrote: [...] > Your query doesn't have an explicit join and is producing a cartesian > result. > I don't think a self- join will work here; a subquery should produce > the result you're after: > SELECT * FROM tmp t1 WHERE NOT EXISTS(SELECT TRUE FROM tmp t2 WHERE > t2.b=t1.a); This produces exactly the result I'm after. I'll need to understand the EXISTS statement there in more detail. Thanks! -- Seb
On 2011-05-14, Seb <spluque@gmail.com> wrote: > Hi, > > This probably reflects my confusion with how self joins work. > > Suppose we have this table: > If I want to get a table with records where none of the values in column > b are found in column a, I thought this should do it: use the "NOT IN" operator with a subquery to retch the disallowed values. select * from tmp where a NOT IN (select b from tmp); -- ⚂⚃ 100% natural
In article <iqne7u$ssa$2@reversiblemaps.ath.cx>, Jasen Betts <jasen@xnet.co.nz> writes: > On 2011-05-14, Seb <spluque@gmail.com> wrote: >> Hi, >> >> This probably reflects my confusion with how self joins work. >> >> Suppose we have this table: >> If I want to get a table with records where none of the values in column >> b are found in column a, I thought this should do it: > use the "NOT IN" operator with a subquery to retch the disallowed > values. > select * from tmp where a NOT IN (select b from tmp); The third way is an OUTER JOIN: SELECT t1.a, t1.b FROM tmp t1 LEFT JOIN tmp t2 ON t2.b = t1.a WHERE t2.b IS NULL;
On 05/14/2011 07:36 PM, Jasen Betts wrote: > > use the "NOT IN" operator with a subquery to retch the disallowed > values.... Hmmm, "retch" as a synonym for "output"? I've seen more than one case where that is an appropriate description. :) Cheers, Steve
On 2011-05-16, Steve Crawford <scrawford@pinpointresearch.com> wrote: > On 05/14/2011 07:36 PM, Jasen Betts wrote: >> >> use the "NOT IN" operator with a subquery to retch the disallowed >> values.... > Hmmm, "retch" as a synonym for "output"? I've seen more than one case > where that is an appropriate description. :) :) was a typo for 'fetch' -- ⚂⚃ 100% natural