Re: self join

Hi

2011/5/15 Seb <spluque(at)gmail(dot)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 t1
WHERE NOT EXISTS(SELECT TRUE FROM tmp t2 WHERE t2.b=t1.a);

HTH

Ian Lawrence Barwick