Joins Versus NOT IN Clause

Kevin Hill explains a potential performance difference between using NOT IN and using a left join:

Basic stuff, right?   Both will return 951 records (books) that I do not own.  And, very quickly…because the tables are tiny.   Sub-1 second is fast.

The issue here is HOW the rows are compared.

English version now, techy stuff later:

In the first query, this is equivalent to you standing at the bookstore and calling home to have someone check to see if the book in your hand is already in your collection.  EVERY time.  One by one.

In the second, you got really smart and brought a list with you, which you are comparing to the books on the shelf at the store.   You’ve got both “lists” in one place, so it is far more efficient.

Even in the case with a few hundred records, you can see why there’d be a performance difference.

Related Posts

Bitwise Logic To Make Values Negative

David Fowler is working with arcane magic: The short answer to the question is to simply apply a bitwise NOT to the number that you want to convert and add 1 to the result.  I wrote about bitwise logic in The Arcane Science of Bitwise Logic and SQL Server but didn’t mention the bitwise NOT. If you […]

Read More

Executing Dynamic SQL: EXEC vs sp_executesql

Tara Kizer gives a scenario where using sp_executesql can result in lower CPU utilization than EXEC: For frequently executed queries, “EXEC sp_executesql @sql”is a good choice but at the risk of encountering parameter sniffing issues. For queries that aren’t executed very often, “EXEC (@sql)” can be used, but you could end up with a bloated […]

Read More

Categories

August 2016
MTWTFSS
« Jul Sep »
1234567
891011121314
15161718192021
22232425262728
293031