Hi,

> Hi Set,
>
> I also came up with the EXISTS solution but after seeing Xavier's post
> I think the IN solution is the quicker one here, because it features
> an independent subselect, that only has to be executed once.
>
> The subselect in our EXISTS solution is not independent; it has to be
> executed for every record in table1. Worse, NOT EXISTS makes it
> necessary to walk through the entire table2 for every record in
> table1. At least that's what we prescribe; I don't know if it would be
> optimized.

Internally the IN is converted to an sub-query which will look like this :

SELECT MYFIELD1 FROM MYTABLE2 WHERE MYTABLE2.MYFIELD1 = MYTABLE1.MYFIELD1

So i don't expect any difference in speed here between IN and EXISTS.


Regards,
Arno Brinkman

> Greetings,
> Paul Vinkenoog
>
>
> >> SELECT * FROM MYTABLE1 WHERE MYTABLE1.MYFIELD1 NOT IN (SELECT
> >> MYFIELD1 FROM MYTABLE2)
> >
> > This will work fine as long as you only have a few records in each
> > table, but the subselect is probably executed once for every row, so
> > you'll notice that this select will become slower as the number of
> > records in the tables increases. Generally, doing
> >
> > SELECT <whatever> FROM MYTABLE1 WHERE NOT EXISTS (SELECT 1 FROM
> > MYTABLE2 WHERE MYTABLE2.<field> = MYTABLE1.<field>)
> >
> > is a lot quicker.
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>