Post reply

Optimizing a NOT IN (...)

  • August 24, 2009 at 12:27 pm

    #134395

    Hi,

    Is there any other (better) way of optimizing a

    SELECT f1 FROM t1 WHERE f1 NOT IN (SELECT f2 FROM t2)

    except the classical :

    SELECT f1 FROM t1 WHERE NOT EXISTS (SELECT f2 FROM t2 WHERE t2.f2 = t1.f1)

    ?

    SELECT f2 FROM t2 returns millions of rows, and NOT EXISTS is better than NOT IN but not exactly fast ...

    I have the right indexes for the tables.

  • August 24, 2009 at 12:41 pm

    #1043827

    virgilrucsandescu (8/24/2009)

    Hi,

    Is there any other (better) way of optimizing a

    SELECT f1 FROM t1 WHERE f1 NOT IN (SELECT f2 FROM t2)

    except the classical :

    SELECT f1 FROM t1 WHERE NOT EXISTS (SELECT f2 FROM t2 WHERE t2.f2 = t1.f1)

    ?

    SELECT f2 FROM t2 returns millions of rows, and NOT EXISTS is better than NOT IN but not exactly fast ...

    I have the right indexes for the tables.

    Try:

    select

    f1

    from

    t1

    left outer join t2

    on (t1.f1 = t2.f2)

    where

    t2.f2 is null;

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply