how to rewrite this query without a subquery ?

[krystoffff]

Hi all

For speed reasons, I would like to rewrite the following query without
the subquery :

SELECT * FROM table1 WHERE field1 NOT IN (SELECT field1 FROM table2
where field2=X);

I thought something like this would work fine :
SELECT table1.* FROM table1 LEFT JOIN table2 ON
table1.field1=table2.field1
WHERE (table2.field2 IS NULL OR table2.field2<>X);

.... but it doesn't, because for example, if you have in table2:
table2.field1 = 1 ; table2.field2 = X
table2.field1 = 1 ; table2.field2 = Y

the second line shoudn't be shown because there IS a line where
table2.field1 = 1 and table2.field2 = X.

With the first query, table2.field1 = 1 WILL NOT be returned (which is
correct) BUT in the second query, it WILL be returned (the join will
skip the first line, but will use the second line) ...

Has anybody a better idea on how to rewrite this kind of query ?!

Thanks for your help
Krystoffff

[krystoffff]

>

SELECT * FROM table1 WHERE field1 NOT IN (SELECT field1 FROM table2
where field2=X);

I maybe found a solution:

select * FROM table1 LEFT JOIN table2 ON (table1.field1=table2.field1
AND table2.field2=X) WHERE table2.field2 IS NULL
The result seems to be correct.
What do you think about ?
krystoffff

[krystoffff]

>

SELECT * FROM table1 WHERE field1 NOT IN (SELECT field1 FROM table2
where field2=X);

I maybe found a solution:

select * FROM table1 LEFT JOIN table2 ON (table1.field1=table2.field1
AND table2.field2=X) WHERE table2.field2 IS NULL
The result seems to be correct.
What do you think about ?
krystoffff

[krystoffff]

>

SELECT * FROM table1 WHERE field1 NOT IN (SELECT field1 FROM table2
where field2=X);

I maybe found a solution:

select * FROM table1 LEFT JOIN table2 ON (table1.field1=table2.field1
AND table2.field2=X) WHERE table2.field2 IS NULL
The result seems to be correct.
What do you think about ?
krystoffff