Another myth is to believe that in subqueries and if statements, exists and in perform faster than not exists and not in when the values in the where clause are not indexed. It was justified by the statement that "For exists and in, SQL Server can return TRUE as soon as a single row matches. For the negated expressions, it must examine all values to determine that there are not matches." (extracted from Sybase SQL Server 11 - Performance and Tuning Guide).
This is obviously false, as for negated expressions, SQL Server will return false as soon as a single row matches, and for exists and in, it must also examine all values to determine that there are not matches. I did extensive tests with timings and query plans to prove it.
They recommended writing:
if exists (select * from table where...)
/* Statement group */
if not exists (select * from table where...)
/* Statement Group */
This optimization tip is not present anymore in the latest versions of the Sybase ASE Performance and Tuning Guide ...
commented on Apr 4 2012 3:00AM