Re: [SQL] comparing 2 tables. . .

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?

Yes, the simple way is to look at the thread 'subselect performance'
a few days ago... :-))  In essence, I don't know... ;)  Something
about that the subselect's result always recomputed for each row in
the main select or what, if I got the idea. (And in the 2nd case the
subselect has only one row, provided that the primary keys are
unique.)

Circum

PS: Anyway, as I can figure it out, this is that 'EXISTS' workaround
that you mentioned in that thread, right?
__  @
/  \    _   _                                           Engárd Ferenc
l    | ( \ /  | | (\/)                      mailto:s-fery@kkt.sote.hu
\__/ | |   \_ \_/ I  I                    http://pons.sote.hu/~s-fery