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(at)kkt(dot)sote(dot)hu
\__/ | | \_ \_/ I I http://pons.sote.hu/~s-fery