From: Jackson, DeJuan <djackson@cpsgroup.com>
To: Eric McKeown <ericm@palaver.net>
To: 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
Date: Mon, 21 Sep 1998 13:07:13 -0500
Message-ID: <F10BB1FAF801D111829B0060971D839F41B5E7@cpsmail> (raw)
> 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
>
>
>
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: djackson@cpsgroup.com, ericm@palaver.net, karl@denninger.net
Subject: Re: [SQL] To create the query for a select where one is NOT in th e other
In-Reply-To: <F10BB1FAF801D111829B0060971D839F41B5E7@cpsmail>
References: <F10BB1FAF801D111829B0060971D839F41B5E7@cpsmail>
* 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