Received: from mail_exchange.westcountrypublications.co.uk ([195.171.163.135]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1JD9KE36511 for ; Tue, 19 Feb 2002 08:09:21 -0500 (EST) (envelope-from SHenshall@westcountrypublications.co.uk) Received: by MAIL_EXCHANGE with Internet Mail Service (5.5.2650.21) id ; Tue, 19 Feb 2002 13:05:06 -0000 Message-ID: From: "Henshall, Stuart - WCP" To: "'tconti@hotmail.com'" , pgsql-sql@postgresql.org Subject: Re: Join Statements Date: Tue, 19 Feb 2002 13:05:05 -0000 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2650.21) Content-Type: text/plain; charset="iso-8859-1" X-Archive-Number: 200202/230 X-Sequence-Number: 6654 > -----Original Message----- > From: tconti@hotmail.com [mailto:tconti@hotmail.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