IN () vs EXISTS () in SqlServer 2005 (or generally in any RDBMS)

Question

I'm trying to figure out which is faster, a clause like "WHERE IN (SELECT 1 FROM MyTable)", or a clause like "WHERE EXISTS (SELECT 1 FROM MyTable).

Let's use the query from the SqlServer documentation:

SELECT * FROM Orders 
WHERE ShipRegion = 'WA' AND EXISTS (
    SELECT EmployeeID FROM Employees AS Emp 
    WHERE Emp.EmployeeID = Orders.EmployeeID)

Or

SELECT * FROM Order
WHERE ShipRegion = 'WA' AND EmployeeID IN (
    SELECT EmployeeID FROM Employees AS Emp 
    WHERE Emp.EmployeeID = Orders.EmployeeID)

I'd like to know the answer, if anyone has it, but I'd really like to know how to test it for myself in SqlServer 2005. (I'm a noob at SqlServer.)

Thanks!

Answer 1

You could also remove the WHERE clause in the IN case:

SELECT * FROM Orders
WHERE ShipRegion = 'WA' AND EmployeeID IN (SELECT EmployeeID FROM Employees)

The query optimizer should be able to generate an identical execution plan for both queries. I'd choose the one that's more readable.

Answer 2

To see for yourself, you can: compare real execution costs, run

SET STATISTICS IO ON
SET STATISTICS TIME ON

then run both queries

Also compare execution plans, highlight both queries and press Ctrl+L and you will see the plans. Most likely you will see identical plans.

Answer 3

The sql sub query although identical will not give you the answer you are looking for as it is co-related and could be changed into a JOIN.

In general EXISTS() should be quicker as it gives a result without having to find any more relations once it has found the first row whereas IN() still has to find subsequent rows until it has finished.

therefore

SELECT * FROM Orders 
WHERE ShipRegion = 'WA' AND EXISTS (
    SELECT 'x' FROM Employees AS Emp 
    WHERE Emp.EmployeeID = 42)

should finish before

SELECT * FROM Order
WHERE ShipRegion = 'WA' AND EmployeeID IN (
    SELECT EmployeeID FROM Employees AS Emp 
    WHERE Emp.EmployeeID = 42)

Answer 4

Using an INNER JOIN would be faster than a subquery:

SELECT * 
  FROM Order o
 INNER JOIN Employees e ON o.EmployeeID = e.EmployeeID
 WHERE ShipRegion = 'WA'

Or with specific criteria:

 SELECT * 
  FROM Order o
 INNER JOIN Employees e ON o.EmployeeID = e.EmployeeID
 WHERE ShipRegion = 'WA'
   AND e.EmployeeID = 42