Restricting rows in WHERE or in ON?
-
I have the choise to restrict the result of a INNER JOIN SELECT statement in the WHERE or in the ON part of the JOIN
E.G.
(simplification of a complex sql statement)
declare @CustomerID varchar(10) set @customerID = 'alfki'
SELECT *
FROM Orders O INNER JOIN
(select * from Customers where CustomerID = @CustomerID) C ON O.CustomerID = C.CustomerID
SELECT *
FROM Orders O INNER JOIN
Customers C ON O.CustomerID = C.CustomerID AND C.CustomerID = @CustomerID
SELECT *
FROM Orders O INNER JOIN
Customers C ON O.CustomerID = C.CustomerID
WHERE C.CustomerID = @CustomerID
Does these solutions always give the same result?
Is there any difference in performance?
Which one is the prefered, if any?
-
The restrict in the JOIN is preferred since it limits the amount of rows the where clause has to process. (like a virtual table)
They do give the same results at the end because it is an inner join. (no so with left,... joins)
If I find the examples I've posted before, I'll put the links in here.
-
And the answer is an astounding "It Depends".
Running the code you've posted against Northwind produces exactly the the same Execution Plan and statistics for all three statements.
So Jo Pattyn, unless you've got something else to add to the pot I'd have to say you're wrong. The query optimiser is intelligent enough to determine that all three statements are requesting the same thing.
Yes the result would be different if an outer join was involved, but this is not the case.
Personally I think it comes down to coding style and I think there would be something in the official standards documentation about filtering being located in the WHERE clause.
--------------------
Colt 45 - the original point and click interface
-
With an INNER JOIN I've never seen it make a difference and hence I prefer to keep the filter in the where clause. (join in the from, filter in the where)
In the case of OUTER JOINs I have seen the placement of the filter change the results of a query. I usually recomend to people to leave filters in the where and if they want to restrict the number of rows before joining, use a subquery.
Changing your example queries to outer join, changing the filter slightly and switching the order of the tables round gave me the following results
declare
@CustomerID varchar(10) set @customerID = 'alfki'
SELECT
*
FROM Customers C LEFT JOIN
(select * from Orders WHERE OrderID IS NOT NULL) O ON O.CustomerID = C.CustomerID
where C.CustomerID = @CustomerID
SELECT
*
FROM Customers C LEFT JOIN
Orders O ON O.CustomerID = C.CustomerID AND OrderID IS NOT NULL
WHERE C.CustomerID = @CustomerID
SELECT
*
FROM Customers C LEFT JOIN
Orders O ON O.CustomerID = C.CustomerID
WHERE C.CustomerID = @CustomerID AND OrderID IS NOT NULL
Query 1 - 34% 6 rows
Query 2 - 34% 6 rows
Query 3 - 31% 6 rows
This is, of course, a very simple example.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks.
You gave me insight in the way to handle this question.
But the real answer is: The query optimiser is so intelligent that it makes no difference (for the INNER JOIN only). I tried it on my "real life" Sql and Execution plan and Statistics are the same.
So I'll use the WHERE solution, for the (only) reason that doing it this way the SQL is more as other people expect. So they can easily find the "restriction part" of the SQL.
-
When we move from inner joins to outer joins, the rules changes accordingly.
(assuming the example with just two tables)
For an inner join, both tables are 'equal', thus if a filter is in the ON or WHERE clause doesn't matter.
Difference with outer joins - both tables not 'equal' - one is 'outer' the other is 'inner'.
eg, for a LEFT JOIN, the first mentioned table is 'outer', the second is 'inner' (left to right, the opposite for a RIGHT JOIN)
eg FROM customers (outer) LEFT JOIN orders (inner)
FROM customers (inner) RIGHT JOIN orders (outer)
So, for outer joins, filtering on columns in the outer table goes in the WHERE clause, filtering on columns in the inner table goes in the ON clause. It's when this gets confused, the results also become confusing - very easy to mess up if not careful.
Consider these two examples, seemingly the same question asked, but in reality very different.
The first returns 267 rows - which is incorrect due to wrong placed inner table filtering - the second 277 rows - which is correct because here the inner table's filter is in the right place.
select *
from customers c
left join orders o
on c.CustomerID = o.CustomerID
where o.ShippedDate > '1998-01-01'
order by o.ShippedDate
go
select *
from customers c
left join orders o
on c.CustomerID = o.CustomerID
and o.ShippedDate > '1998-01-01'
order by o.ShippedDate
go
/Kenneth
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply