THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

Delimited List to Table - Part II

In this entry, we will explore a little oddity related to how we join two sets of data together. As you may already know, you can join two tables together in a variety of ways, three of which are shown here.

SELECT *
FROM Orders
WHERE OrderID IN (SELECT OrderID FROM [Order Details] Where ProductID = 6)

SELECT *
FROM Orders O
 INNER JOIN (SELECT OrderID FROM [Order Details] Where ProductID = 6) OL
 ON O.OrderID = OL.OrderID

SELECT *
FROM Orders O
 INNER JOIN [Order Details] OL
  ON O.OrderID = OL.OrderID
WHERE OL.ProductID = 6

These will all return the same results, but the last one (the one not using derived tables) actually performs worse than the other two, which perform equally (and even use the same execution plan). It has to do with the way the query optimizer decides to attack the query. So, knowing this, you might suspect the following two queries to work the same behind the scenes.

SELECT *
FROM Orders O
 INNER JOIN
 (SELECT CAST(ListValue AS int) AS OrderID FROM dbo.fnSplit2Table(@sOrderIDs, @sDelimeter)) OL
  ON O.OrderID = OL.OrderID

SELECT *
FROM Orders O
WHERE OrderID IN (SELECT CAST(ListValue AS int) FROM dbo.fnSplit2Table(@sOrderIDs, @sDelimeter))

Alas, it isn't true, and the former, which uses the INNER JOIN, actually performs better than its counterpart.

This supports my assertion that an INNER JOIN will have equal or better performance than an equivalently stated WHERE clause using an IN clause in the criteria. There have been many other cases I have found this to be the case. The use of the table-valued UDF brings out the performance difference quite well here.

Published Monday, June 26, 2006 12:59 PM by Peter W. DeBetta

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Peter W. DeBetta

Peter DeBetta works for Microsoft as an Application Platform Technology Strategist, specializing in strategies, design, implementation, and deployment of Microsoft SQL Server and .NET solutions. Peter writes courseware, articles, and books – most recently the title Introducing SQL Server 2008 from Microsoft Press. Peter speaks at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar (click here to hear an original song by Peter), taking pictures, or simply enjoying life with his wife, son, and daughter.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement