Should I use IN or OR to get the best performance from MS SQL Server
What to do?
- Some SQL books recommend using the IN operator.
- Some SQL Books recommend using the OR operator.
Data used
USE sql911;
go
SELECT sales_date, sales_qty, emp_short
FROM sales
ORDER BY emp_short;
go
go
SELECT sales_date, sales_qty, emp_short
FROM sales
ORDER BY emp_short;
go
sales_date sales_qty emp_short ----------------------- --------------------------------------- ---------- 2005-12-06 11:01:01.000 8.00 000010 2006-05-06 16:01:01.000 3.00 000010 2005-11-06 10:01:01.000 9.00 000010 2007-06-06 17:01:01.000 2.00 000010 2007-04-06 00:00:00.000 4.00 000010 2005-04-06 15:01:01.000 4.00 000010 2006-04-06 15:01:01.000 4.00 000010 2006-07-06 19:01:01.000 1.00 000010 2007-11-06 10:01:01.000 9.00 000030 2006-06-06 17:01:01.000 2.00 000030 2005-10-06 10:01:01.000 10.00 000030 2006-04-06 15:01:01.000 4.00 000030 2006-01-06 12:01:01.000 7.00 000030 2006-10-06 10:01:01.000 10.00 000030 2007-05-01 00:00:00.000 5.00 000030 2004-03-06 14:01:01.000 5.00 000110 2006-06-06 17:01:01.000 2.00 000110 2006-02-06 13:01:01.000 6.00 000110 2006-10-06 10:01:01.000 10.00 000110 2006-10-06 10:01:01.000 3.00 000110 2006-03-06 14:01:01.000 5.00 000110 2005-11-06 10:01:01.000 9.00 000110 2006-10-06 10:01:01.000 10.00 000190 2006-10-06 10:01:01.000 10.00 000190 2006-02-06 13:01:01.000 6.00 000190 2005-03-06 14:01:01.000 5.00 000190 2006-06-06 17:01:01.000 2.00 000190 2006-07-06 19:01:01.000 1.00 000190 2005-12-06 11:01:01.000 8.00 000190 2005-05-06 16:01:01.000 3.00 000190 2007-07-06 00:00:00.000 1.00 000190 2006-07-06 00:00:00.000 1.00 000190 2006-07-06 19:01:01.000 1.00 000310 2006-03-06 14:01:01.000 5.00 000310 2006-01-06 12:01:01.000 7.00 000310 2004-10-06 10:01:01.000 10.00 000310 2006-10-06 10:01:01.000 10.00 000310 2005-10-06 10:01:01.000 10.00 000310 2006-10-06 10:01:01.000 10.00 000310 (39 row(s) affected)
Using the IN operator with MS SQL Server
SELECT sales_date, sales_qty, emp_short
FROM sales
WHERE emp_short in ('000310','000190')
ORDER BY emp_short;
go
FROM sales
WHERE emp_short in ('000310','000190')
ORDER BY emp_short;
go
sales_date sales_qty emp_short ----------------------- --------------------------------------- ---------- 2006-02-06 13:01:01.000 6.00 000190 2006-10-06 10:01:01.000 10.00 000190 2006-10-06 10:01:01.000 10.00 000190 2006-06-06 17:01:01.000 2.00 000190 2005-03-06 14:01:01.000 5.00 000190 2007-07-06 00:00:00.000 1.00 000190 2005-05-06 16:01:01.000 3.00 000190 2006-07-06 19:01:01.000 1.00 000190 2005-12-06 11:01:01.000 8.00 000190 2006-07-06 00:00:00.000 1.00 000190 2006-07-06 19:01:01.000 1.00 000310 2006-03-06 14:01:01.000 5.00 000310 2006-01-06 12:01:01.000 7.00 000310 2005-10-06 10:01:01.000 10.00 000310 2006-10-06 10:01:01.000 10.00 000310 2006-10-06 10:01:01.000 10.00 000310 2004-10-06 10:01:01.000 10.00 000310 (17 row(s) affected)
Worked properly, but is it the most efficient? Let's look at the execution plan:
In Execution plan
Execution Plan for the IN query
Notice in the yellow box at the right:
Predicate: [emp_short] = '000310' or [emp_short] = '000190'
Conclusion
- Microsoft SQL Server rewrites the IN query to an OR query.
- It does not matter which one you use, it will be rewritten to an OR query.
- Use the one that is the easiest for you to read and later maintain.
- I personally prefer the IN query.