In the following stament is there any point including the DISTINCT keyword or will all query optimisers realise that only distinct rows need to be selected in the subselect?

Thanks in advance

it's superfluous, if you are speaking of the intended meaning (semantics of the statement)

however, if you are referring to performance, it may or may not speed up the query, depending on which database system you try this in

Thanks for the reply.

So depending on the DBMS this will either improve query performance or have no effect on performance at all (but it will never decrease performance). IMHO it would therefore be sensible to always include the DISTINCT keyword in these subselects. Do you agree/disagree?

No, it can definitely decrease performance because some database engines will wait for the entire result set to materialize if DISTINCT is added.

I think that the correct way to handle this type of code is to correct it by changing the IN clause to an EXISTS clause and removing the DISTINCT operator. This reduces the SQL statement to primatives, which any database engine should be able to optimize for best performance.

-PatP

You can also use an outer join. This query isn't exactly equivalent to the NOT IN version because it ignores nulls in col2, if any. It probably gives the result you wanted however:

SELECT
...
FROM tableA
LEFT JOIN tableB
ON tableA.col1 = tableB.col2
WHERE tableB.col2 IS NOT NULL;