Filtering Data in SQL
In early versions of SQL Server, ported into Windows by Microsoft from the Sybase code, both filtering and join criteria were coded in the WHERE clause. Here is a short example…
SELECT …
FROM PurchaseOrder po
PurshaseOrderDetail pod
WHERE
po.PurchaseOrderID = pod.PurchaseOrderID
AND po.CustomerID = @CustomerID
As you can see the filter for the PurchaseOrder table on CustomerID is included in the where clause just as the join criteria for the PurchaseOrder to the PurchaseOrderDetil tables.
Later versions of MS SQL Server introduced more recent join syntax where would now write the same query as follows
SELECT …
FROM PurchaseOrder po
JOIN PurshaseOrderDetail pod ON po.PurchaseOrderID = pod.PurchaseOrderID
WHERE po.CustomerID = @CustomerID
Consider the condition when you need to introduce a filter on the Detail table as well as the join criteria. Say you want to return only Detail records for a specific item. You have the option of placing this criteria in either the JOIN clause or in the WHERE clause. Try it yourself and compare the query plan for either version. They are not always the same.
SELECT …
FROM PurchaseOrder po
JOIN PurshaseOrderDetail pod ON po.PurchaseOrderID = pod.PurchaseOrderID
AND pod.ItemID = @ItemID
WHERE po.CustomerID = @CustomerID
or
SELECT …
FROM PurchaseOrder po
JOIN PurshaseOrderDetail pod ON po.PurchaseOrderID = pod.PurchaseOrderID
WHERE po.CustomerID = @CustomerID
AND pod.ItemID = @ItemID
There are some good reasons to choose one technique over the other depending on circumstances. The results are definitely not always consistent…so you need to be aware of what you are doing, and what your intended result should be.
Take a look when you have a moment. Share your preference by add comments below, or feel free to send comments to btaylor@sswug.org.
Reader Response – The Art of Programming
Milton Writes:
"Formation is what lasts when you forget what you learned"
This is a quote, by a Brazilian educator, that I use when I’m in classes with tech people. From a long term perspective (even considering that, according to Keynes, in the long term we will all be dead), I believe that this is what really matters.
That’s what professor Knuth is talking about.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
SQL Server index related dynamic management views and functions – (Part 3)
This is the part-3 of this three part article series on index-related dynamic management and functions. In this part, I’ll be covering following index-related dynamic management views (DMVs) that are helpful to find missing indexes inside databases.
Featured White Paper(s)
Harness Your Data for Better, Faster Decision-Making
read more)