Pentru joncțiuni INNER, un răspuns este oferit de
Books Online:
"There can be predicates that involve only
one of the joined tables in the ON clause. Such predicates also can be
in the WHERE clause in the query.
Although the placement of such
predicates does not make a difference for INNER joins, they might cause a
different result when OUTER joins are involved. This is because the
predicates in the ON clause are applied to the table before the join,
whereas the WHERE clause is semantically applied to the result of the
join.
". Este posibil, totuși, ca BOL/MSDN să facă referire la rezultatele joncțiunii și nu la performanță.
Pentru jocțiunile INNER se pot face următoarele teste care demonstrează că (în principiu) nu există nici un câștig/o penalizare (cel puțin în SQL Server 2008) dacă scriu condiția în WHERE sau în ON.
Versiune SQL Server: 2008
Bază de date:
AdventureWorks2008Interogări utilizate:
-- Test 1
PRINT '***** Q1.1 *****'
SELECT COUNT(h.AccountNumber)
FROM Sales.SalesOrderHeader h
INNER JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
WHERE d.SpecialOfferID = 2;
PRINT '***** Q1.2 *****'
SELECT COUNT(h.AccountNumber)
FROM Sales.SalesOrderHeader h
INNER JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
AND d.SpecialOfferID = 2;
-- Test 2
PRINT '***** Q2.1 *****'
SELECT COUNT(*)
FROM Sales.SalesOrderHeader h
INNER JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesPersonID = 277;
PRINT '***** Q2.2 *****'
SELECT COUNT(*)
FROM Sales.SalesOrderHeader h
INNER JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
AND h.SalesPersonID = 277;-- Test 3
PRINT '***** Q3.1 *****'
SELECT COUNT(*)
FROM Sales.SalesOrderHeader h
INNER JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesPersonID = 277
AND YEAR(h.OrderDate) = 2001;
PRINT '***** Q3.2 *****'
SELECT COUNT(*)
FROM Sales.SalesOrderHeader h
INNER JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
AND h.SalesPersonID = 277
AND YEAR(h.OrderDate) = 2001;
PRINT '***** Q3.3 *****'
SELECT COUNT(*)
FROM Sales.SalesOrderHeader h
INNER JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
AND h.SalesPersonID = 277
WHERE YEAR(h.OrderDate) = 2001;Rezultate SET STATISTICS IO (panoul "Messages"): identice
Planurile de execuţie: identice