TSQL Filtering Examples
Today I wanted to demonstrate a couple simple scenarios where you have options for adding filtering criteria or where you must place the criteria in the JOIN clause.
For my first example I want to return all the purchase orders having at least one Purchase Order Detail with an item of 154 (shoes). The following two queries result in identical query plans…
DECLARE @ShoesItemID INT = 154
SELECT PO.PurchaseOrderID
FROM PurchaseOrder PO
JOIN PurchaseOrderDetail POD ON PO.PurchaseOrderID = POD.PurchaseOrderID
AND POD.ItemID = @ShoesItemID
WHERE PO.PurchaseOrderID BETWEEN 10 and 2000
OR
SELECT PO.PurchaseOrderID
FROM PurchaseOrder PO
JOIN PurchaseOrderDetail POD ON PO.PurchaseOrderID = POD.PurchaseOrderID
WHERE PO.PurchaseOrderID BETWEEN 10 and 2000
AND POD.ItemID = @ShoesItemID
In my database, regardless of available indexes, or index tuning, the query plan for both queries is identical. This result is found in SQL Server. It may not be the same on other query engines.
Lets mnodify the requirements a little bit. How about returning all Purchase Orders that have no Purchase Order Detail for the item 154 (Shoes). To do this we use an outer join (LEFT JOIN) to join the Purchase Order table to the Purchase Order Detail table. In this case it is essential that you place the filter criteria in the JOIN clause.
DECLARE @ShoesItemID INT = 154
SELECT PO.PurchaseOrderID
FROM PurchaseOrder PO
LEFT JOIN PurchaseOrderDetail POD ON PO.PurchaseOrderID = POD.PurchaseOrderID
AND POD.ItemID = @ShoesItemID
WHERE PO.PurchaseOrderID BETWEEN 10 and 2000
AND POD.PurchaseOrderID IS NULL
Changing the query to the following code is the same as using an INNER JOIN method resulting in no Purchase Orders being returned.
DECLARE @ShoesItemID INT = 154
SELECT PO.PurchaseOrderID
FROM PurchaseOrder PO
LEFT JOIN PurchaseOrderDetail POD ON PO.PurchaseOrderID = POD.PurchaseOrderID
WHERE PO.PurchaseOrderID BETWEEN 10 and 2000
AND POD.ItemID = @ShoesItemID
AND POD.PurchaceOrderID IS NULL
This pretty much demonstrates what Adrian stated yesterday about how an INNER JOIN won’t really have a difference in the query plan or output, whereas an OUTER JOIN (FULL, LEFT, RIGHT) will benefit from placing filter criteria in the JOIN clause.
Here’s a little quiz for the day…
What is the result of a Cartesian join (CROSS JOIN in TSQL)? Have you found a use for this feature? Drop a note to btaylor@sswug.org with you ideas. You can also post a response below if comments are available.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Tips for using Very Large Databases in SQL Server 2012
In this article, you can find some useful Very Large Databases performance tuning and optimization tips.
Featured White Paper(s)
How to Use SQL Server’s Extended Events and Notifications to Proactively Resolve Performance Issues
read more)