pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Henshall, Stuart - WCP <SHenshall@westcountrypublications.co.uk>
To: 'tconti@hotmail.com' <tconti@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Join Statements
Date: Tue, 19 Feb 2002 13:05:05 -0000
Message-ID: <E2870D8CE1CCD311BAF50008C71EDE8E01F747EB@MAIL_EXCHANGE> (raw)



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



view thread (4+ messages)  latest in thread

Message-ID: <E2870D8CE1CCD311BAF50008C71EDE8E01F747EB@MAIL_EXCHANGE>
Permalink:  ../E2870D8CE1CCD311BAF50008C71EDE8E01F747EB@MAIL_EXCHANGE/
Also on:    postgresql.org/message-id/E2870D8CE1CCD311BAF50008C71EDE8E01F747EB@MAIL_EXCHANGE

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: SHenshall@westcountrypublications.co.uk, tconti@hotmail.com
  Subject: Re: Join Statements
  In-Reply-To: <E2870D8CE1CCD311BAF50008C71EDE8E01F747EB@MAIL_EXCHANGE>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox