Re: [SQL] comparing 2 tables. . .

Well, I guess officially you're supposed to be able to decode an EXPLAIN
of each statement but I'm not quite that savvy (yet).

With regards to this specific examples, I seem to remember a number of
previous postings that say that (NOT) EXISTS type statements are much
faster that (NOT) IN (SELECT...) type statements (because that kind of
sub-select is currently sub-optimally implemented in PG).

regards,

Stuart.

On Fri, 24 Sep 1999, Albert REINER wrote:

> On Fri, Sep 24, 1999 at 03:32:51PM +0100, Stuart Rison wrote:
> ...
> > Try:
> > 
> > SELECT tab1.pkey FROM <your_table> tab1 WHERE tab1.pkey NOT IN (SELECT
> > tab2.pkey FROM <your_other_table> tab2.pkey);
> > 
> > or else (faster):
> > 
> > SELECT tab1.pkey FROM <your_table> tab1 WHERE NOT EXISTS (SELECT 1 FROM
> > <your_other_table> tab2 WHERE tab1.pkey=tab2.pkey);
> 
> Maybe this is trivial, but...
> 
> Is there a simple way to see that/why the last query is faster?
> 
> Albert.
> 
> -- 
> 
> ---------------------------------------------------------------------------
>   Post an / Mail to / Skribu al: Albert Reiner <areiner@tph.tuwien.ac.at>
> ---------------------------------------------------------------------------
> 
> ************
> 

Stuart C. G. Rison
Department of Biochemistry and Molecular Biology
6th floor, Darwin Building, University College London (UCL)
Gower Street, London, WC1E 6BT, United Kingdom
Tel. 0207 504 2303, Fax. 0207 380 7033
e-mail: rison@biochem.ucl.ac.uk