pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Join Statements
4+ messages / 4 participants
[nested] [flat]

* Join Statements
@ 2002-02-11 15:15 T Conti <tconti@hotmail.com>
  2002-02-16 18:30 ` Re: Join Statements Mark Nielsen <python@kepnet.net>
  0 siblings, 1 reply; 4+ messages in thread

From: tconti@hotmail.com @ 2002-02-11 15:15 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Join Statements
  2002-02-11 15:15 Join Statements T Conti <tconti@hotmail.com>
@ 2002-02-16 18:30 ` Mark Nielsen <python@kepnet.net>
  2002-02-26 17:20   ` Re: Join Statements Andy Marden <amarden@usa.net>
  0 siblings, 1 reply; 4+ messages in thread

From: Mark Nielsen @ 2002-02-16 18:30 UTC (permalink / raw)
  To: pgsql-sql

select distinct TABLENAME_id from TABLENAME_backup where  NOT 
TABLENAME_id = ANY (select TABLENAME_id from TABLENAME)

Here is an example of me getting all the ids from the backup table that 
doesn't exist in the main table. This is similar to what you want.

I think I am answering your question, or close to it.
Mark

T Conti wrote:

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Join Statements
  2002-02-11 15:15 Join Statements T Conti <tconti@hotmail.com>
  2002-02-16 18:30 ` Re: Join Statements Mark Nielsen <python@kepnet.net>
@ 2002-02-26 17:20   ` Andy Marden <amarden@usa.net>
  0 siblings, 0 replies; 4+ messages in thread

From: Andy Marden @ 2002-02-26 17:20 UTC (permalink / raw)
  To: pgsql-sql

Your problem here is in the use of the use of '!='. All the columns from b
will be null if there is no match. Any null result always fails a test so
null is never not equal to anything (or equal to it for that matter!). Don't
get me started on NULLs any the ludicrous ways that RDBMSs deal them them!

What you need is:

select count(a.*)
from a left outer join b on a.id = b.id
where b.id is null

This will give you the best performing query rather than NOT IN sub query.
It also gives you greater control of the query plan. Internally, these days,
this is what Oracle converts a not in sub-query to if you let it. PostgreSQL
still persists with the old way, it seems, of doing a nested-loop scan of
the sub query for each row in the outer which is usually horribly
inefficient.

The alternative solution below with the NOT ... ANY seems to give the same
query plan as the NOT IN approach

Cheers

Andy

"Mark Nielsen" <python@kepnet.net> wrote in message
news:3C6EA55C.8040905@kepnet.net...
> select distinct TABLENAME_id from TABLENAME_backup where  NOT
> TABLENAME_id = ANY (select TABLENAME_id from TABLENAME)
>
> Here is an example of me getting all the ids from the backup table that
> doesn't exist in the main table. This is similar to what you want.
>
> I think I am answering your question, or close to it.
> Mark
>
> T Conti wrote:
>
> > 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
> >
>
>





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Join Statements
@ 2002-02-19 13:05 Henshall, Stuart - WCP <SHenshall@westcountrypublications.co.uk>
  0 siblings, 0 replies; 4+ messages in thread

From: Henshall, Stuart - WCP @ 2002-02-19 13:05 UTC (permalink / raw)
  To: 'tconti@hotmail.com' <tconti@hotmail.com>; pgsql-sql



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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2002-02-26 17:20 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-11 15:15 Join Statements T Conti <tconti@hotmail.com>
2002-02-16 18:30 ` Mark Nielsen <python@kepnet.net>
2002-02-26 17:20   ` Andy Marden <amarden@usa.net>
2002-02-19 13:05 Re: Join Statements Henshall, Stuart - WCP <SHenshall@westcountrypublications.co.uk>

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