Re: Join Statements

From: "Henshall, Stuart - WCP" <SHenshall(at)westcountrypublications(dot)co(dot)uk>
To: "'tconti(at)hotmail(dot)com'" <tconti(at)hotmail(dot)com>, pgsql-sql(at)postgresql(dot)org
Subject: Re: Join Statements
Date: 2002-02-19 13:05:05
Message-ID: E2870D8CE1CCD311BAF50008C71EDE8E01F747EB@MAIL_EXCHANGE
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

> -----Original Message-----
> From: tconti(at)hotmail(dot)com [mailto:tconti(at)hotmail(dot)com]
> Sent: 11 February 2002 15:16

>
>
> Howdy:
>
> I need to put together an SQL statement that will return all of the
> rows in table A that are not in table B. Table A and B have the same
> primary key. For example:
>
> select count(a.*)
> from a (nolock) left outer join
> b (nolock) on a.id = b.id
> where a.id != b.id
>
> This did not work. It returned 0 rows. I know that this could be
> done very easily in a sub-select, but that seems inefficient. Is
> there any way to accomplish what I mentioned above in the join
> statement or is the sub-select the way to go?
>
> Thanks for the help,
> Tom
>

This shouldn't be too inefficient:
select * from a where NOT EXISTS (SELECT * FROM b WHERE b.id=a.id)
Note the use of EXISTS rather than IN (IN isn't efficient on PostgreSQL)
If you wanted to use a join I think this is what you actually want:
SELECT * FROM a LEFT JOIN b ON a.id=b.id WHERE b.id IS NULL;
The reason a.id<>b.id doesn't work is because NULL is an unknown value, and
therefore might equal a if it was known.
Give it a test and tell me which works better (I'd expect them to be about
the same).
Cheers,
- Stuart

Browse pgsql-sql by date

  From Date Subject
Next Message D'laila Pereira 2002-02-19 13:57:49 SQL query (general)
Previous Message Luis Sousa 2002-02-19 12:20:38 Re: SELECT with LEFT OUTER JOIN ON