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.

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.