pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
To create the query for a select where one is NOT in the other
5+ messages / 3 participants
[nested] [flat]

* To create the query for a select where one is NOT in the other
@ 1998-09-19 22:55 Karl Denninger <karl@denninger.net>
  1998-09-20 23:23 ` Re: [SQL] To create the query for a select where one is NOT in the other Eric McKeown <ericm@palaver.net>
  0 siblings, 1 reply; 5+ messages in thread

From: Karl Denninger @ 1998-09-19 22:55 UTC (permalink / raw)
  To: pgsql-sql

I want to specify the following SELECT and can't figure it out:

	Table "a" and table "b" have identical schemas

	I want to select all the records in "a" in which a key field in "a"
	does NOT have a corresponding row in "b".

	I know how to do this if I want the opposite, that is, if I want all
	the rows in "a" in which the key IS present in "b" this works:

	select a.field1, a.field2 from t1 a, t2 b where a.field1 = b.field1;

However, the intuitive modification of this (change "=" to "<>") produces
a monster result in which each row in "a" other than the one which matches
gets output for each instance of "b" (which if you think about it does 
make sense).

How do I structure an SQL statement to get the desired rows returned?

--
-- 
Karl Denninger (karl@denninger.net)

"Yes, the president should resign. He has lied to the American people,
time and time again, and betrayed their trust.  Since he has
admitted guilt, there is no reason to put the American people through
an impeachment.  He will serve absolutely no purpose in finishing out
his term, the only possible solution is for the president to save 
some dignity and resign."
________________________________________________________________________

12th Congressional District Hopeful William Jefferson Clinton,
during the Nixon investigations




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

* Re: [SQL] To create the query for a select where one is NOT in the other
  1998-09-19 22:55 To create the query for a select where one is NOT in the other Karl Denninger <karl@denninger.net>
@ 1998-09-20 23:23 ` Eric McKeown <ericm@palaver.net>
  0 siblings, 0 replies; 5+ messages in thread

From: Eric McKeown @ 1998-09-20 23:23 UTC (permalink / raw)
  To: Karl Denninger <karl@denninger.net>; +Cc: pgsql-sql

On Sat, 19 Sep 1998, Karl Denninger wrote:

> Date: Sat, 19 Sep 1998 17:55:14 -0500
> From: Karl Denninger <karl@denninger.net>
> To: pgsql-sql@postgreSQL.org
> Subject: [SQL] To create the query for a select where one is NOT in the other 
> 
> I want to specify the following SELECT and can't figure it out:
> 
> 	Table "a" and table "b" have identical schemas
> 
> 	I want to select all the records in "a" in which a key field in "a"
> 	does NOT have a corresponding row in "b".
> 
> 	I know how to do this if I want the opposite, that is, if I want all
> 	the rows in "a" in which the key IS present in "b" this works:
> 
> 	select a.field1, a.field2 from t1 a, t2 b where a.field1 = b.field1;

I think what you need to do is use a subquery:

select key_field from table1 where key_field NOT IN (select key_field from
table2) ;

The subquery should select a list of all the keys in table 2, and then no
key from talbe 1 that matches anything in this list will be included in
your search results.

Make sense??

eric

> 
> However, the intuitive modification of this (change "=" to "<>") produces
> a monster result in which each row in "a" other than the one which matches
> gets output for each instance of "b" (which if you think about it does 
> make sense).
> 
> How do I structure an SQL statement to get the desired rows returned?
> 
> --
> -- 
> Karl Denninger (karl@denninger.net)
> 
> "Yes, the president should resign. He has lied to the American people,
> time and time again, and betrayed their trust.  Since he has
> admitted guilt, there is no reason to put the American people through
> an impeachment.  He will serve absolutely no purpose in finishing out
> his term, the only possible solution is for the president to save 
> some dignity and resign."
> ________________________________________________________________________
> 
> 12th Congressional District Hopeful William Jefferson Clinton,
> during the Nixon investigations
> 
> 
> 

_______________________
Eric McKeown
ericm@palaver.net
http://www.palaver.net






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

* RE: [SQL] To create the query for a select where one is NOT in th e other
@ 1998-09-21 18:07 Jackson, DeJuan <djackson@cpsgroup.com>
  1998-09-22 20:49 ` RE: [SQL] To create the query for a select where one is NOT in th	e other Eric McKeown <ericm@palaver.net>
  0 siblings, 1 reply; 5+ messages in thread

From: Jackson, DeJuan @ 1998-09-21 18:07 UTC (permalink / raw)
  To: Eric McKeown <ericm@palaver.net>; Karl Denninger <karl@denninger.net>; +Cc: pgsql-sql

> On Sat, 19 Sep 1998, Karl Denninger wrote:
> 
> > Date: Sat, 19 Sep 1998 17:55:14 -0500
> > From: Karl Denninger <karl@denninger.net>
> > To: pgsql-sql@postgreSQL.org
> > Subject: [SQL] To create the query for a select where one is NOT in
> the other 
> > 
> > I want to specify the following SELECT and can't figure it out:
> > 
> > 	Table "a" and table "b" have identical schemas
> > 
> > 	I want to select all the records in "a" in which a key field in
> "a"
> > 	does NOT have a corresponding row in "b".
> > 
> > 	I know how to do this if I want the opposite, that is, if I want
> all
> > 	the rows in "a" in which the key IS present in "b" this works:
> > 
> > 	select a.field1, a.field2 from t1 a, t2 b where a.field1 =
> b.field1;
> 
> I think what you need to do is use a subquery:
> 
> select key_field from table1 where key_field NOT IN (select key_field
> from
> table2) ;
> 
This query will run faster and get you the same results:
SELECT key_field FROM table1 a WHERE NOT EXISTS(SELECT b.key_field FROM
table2 b WHERE b.key_field = a.key_field);

The reason query two is faster than one is two fold...
1) The second query's subquery will only return row's if and only if
there is a match, unlike the first query which will have to return every
row in table2 for every row in table1 and then compare table1.key_field
to every one of those values.
2) The first query will not use indexes at all.  Where as the second one
will for the subquery and a table scan for table1. 

I suggest timing both queries with and without indexes.  And use
'explain' to see the query plans.  Most IN/NOT IN queries can be written
as an EXISTS/NOT EXISTS query and run faster.

> The subquery should select a list of all the keys in table 2, and then
> no
> key from talbe 1 that matches anything in this list will be included
> in
> your search results.
> 
> Make sense??
> 
> eric
> 
> > 
> > However, the intuitive modification of this (change "=" to "<>")
> produces
> > a monster result in which each row in "a" other than the one which
> matches
> > gets output for each instance of "b" (which if you think about it
> does 
> > make sense).
> > 
> > How do I structure an SQL statement to get the desired rows
> returned?
> > 
> > --
> > -- 
> > Karl Denninger (karl@denninger.net)
> > 
> > "Yes, the president should resign. He has lied to the American
> people,
> > time and time again, and betrayed their trust.  Since he has
> > admitted guilt, there is no reason to put the American people
> through
> > an impeachment.  He will serve absolutely no purpose in finishing
> out
> > his term, the only possible solution is for the president to save 
> > some dignity and resign."
> >
> ______________________________________________________________________
> __
> > 
> > 12th Congressional District Hopeful William Jefferson Clinton,
> > during the Nixon investigations
> > 
> > 
> > 
> 
> _______________________
> Eric McKeown
> ericm@palaver.net
> http://www.palaver.net
> 
> 
> 



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

* RE: [SQL] To create the query for a select where one is NOT in th	e other
  1998-09-21 18:07 RE: [SQL] To create the query for a select where one is NOT in th e other Jackson, DeJuan <djackson@cpsgroup.com>
@ 1998-09-22 20:49 ` Eric McKeown <ericm@palaver.net>
  0 siblings, 0 replies; 5+ messages in thread

From: Eric McKeown @ 1998-09-22 20:49 UTC (permalink / raw)
  To: Jackson, DeJuan <djackson@cpsgroup.com>; +Cc: Karl Denninger <karl@denninger.net>; pgsql-sql

On Mon, 21 Sep 1998, Jackson, DeJuan wrote:

> Date: Mon, 21 Sep 1998 13:07:13 -0500
> From: "Jackson, DeJuan" <djackson@cpsgroup.com>
> To: Eric McKeown <ericm@palaver.net>, Karl Denninger <karl@denninger.net>
> Cc: pgsql-sql@postgreSQL.org
> Subject: RE: [SQL] To create the query for a select where one is NOT in th	e other 
> 
> > On Sat, 19 Sep 1998, Karl Denninger wrote:
> > 
> > I think what you need to do is use a subquery:
> > 
> > select key_field from table1 where key_field NOT IN (select key_field
> > from
> > table2) ;
> > 
> This query will run faster and get you the same results:
> SELECT key_field FROM table1 a WHERE NOT EXISTS(SELECT b.key_field FROM
> table2 b WHERE b.key_field = a.key_field);

Bear with me; I'm learning, and I have a couple of questions.

> 
> The reason query two is faster than one is two fold...
> 1) The second query's subquery will only return row's if and only if
> there is a match, unlike the first query which will have to return every
> row in table2 for every row in table1 and then compare table1.key_field
> to every one of those values.

I understand that--makes perfect sense.

> 2) The first query will not use indexes at all.  Where as the second one
> will for the subquery and a table scan for table1. 
> 
> I suggest timing both queries with and without indexes.  And use
> 'explain' to see the query plans.  Most IN/NOT IN queries can be written
> as an EXISTS/NOT EXISTS query and run faster.

So what is the main difference between IN/NOT IN and EXISTS/NOT EXISTS
queries?  The fact that IN/NOT IN doesn't use indexes and EXISTS/NOT
EXISTS does?  Is EXISTS/NOT EXISTS standard SQL syntax?  

Thanks for the pointers...
_______________________
Eric McKeown
ericm@palaver.net
http://www.palaver.net




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

* RE: [SQL] To create the query for a select where one is NOT in th e other
@ 1998-09-22 23:09 Jackson, DeJuan <djackson@cpsgroup.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Jackson, DeJuan @ 1998-09-22 23:09 UTC (permalink / raw)
  To: Eric McKeown <ericm@palaver.net>; +Cc: Karl Denninger <karl@denninger.net>; pgsql-sql

> On Mon, 21 Sep 1998, Jackson, DeJuan wrote:
> > > On Sat, 19 Sep 1998, Karl Denninger wrote:
> > > I think what you need to do is use a subquery:
> > > 
> > > select key_field from table1 where key_field NOT IN (select
> key_field
> > > from
> > > table2) ;
> > > 
> > This query will run faster and get you the same results:
> > SELECT key_field FROM table1 a WHERE NOT EXISTS(SELECT b.key_field
> FROM
> > table2 b WHERE b.key_field = a.key_field);
> 
> Bear with me; I'm learning, and I have a couple of questions.
> 
> > 
> > The reason query two is faster than one is two fold...
> > 1) The second query's subquery will only return row's if and only if
> > there is a match, unlike the first query which will have to return
> every
> > row in table2 for every row in table1 and then compare
> table1.key_field
> > to every one of those values.
> 
> I understand that--makes perfect sense.
> 
> > 2) The first query will not use indexes at all.  Where as the second
> one
> > will for the subquery and a table scan for table1. 
> > 
> > I suggest timing both queries with and without indexes.  And use
> > 'explain' to see the query plans.  Most IN/NOT IN queries can be
> written
> > as an EXISTS/NOT EXISTS query and run faster.
> 
> So what is the main difference between IN/NOT IN and EXISTS/NOT EXISTS
> queries?  The fact that IN/NOT IN doesn't use indexes and EXISTS/NOT
> EXISTS does?  Is EXISTS/NOT EXISTS standard SQL syntax?  
> 
> Thanks for the pointers...
> 
OK PostgreSQL version before 6.4 did not have OR indexing, and if you
think about an IN or a NOT IN expression it can be expressed as a series
of OR-equal.  So, it's possible that PosterSQL would be able to use
indexes on the query but I doubt the it would.  The EXISTS operator does
not use indexes either, but it is a single boolean operator that only
has to test for a single tuple or row return, which can be much faster
than a row by row boolean compare.  It was the rewrite of the subquery
that got the index speed bonus.  The first subquery had to bring back
every row in table2 for every row in table1 (also read table-scan on
table2), where as the second subquery only had to bring back the
matching rows.  As long as key_field on table2 has an index the database
should use it.

I don't have the ANSI SQL standard in front of me but every database,
commercial or otherwise, that I have used has had the EXISTS clause.



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


end of thread, other threads:[~1998-09-22 23:09 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-09-19 22:55 To create the query for a select where one is NOT in the other Karl Denninger <karl@denninger.net>
1998-09-20 23:23 ` Eric McKeown <ericm@palaver.net>
1998-09-21 18:07 RE: [SQL] To create the query for a select where one is NOT in th e other Jackson, DeJuan <djackson@cpsgroup.com>
1998-09-22 20:49 ` RE: [SQL] To create the query for a select where one is NOT in th	e other Eric McKeown <ericm@palaver.net>
1998-09-22 23:09 RE: [SQL] To create the query for a select where one is NOT in th e other Jackson, DeJuan <djackson@cpsgroup.com>

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