question

swethaashwini avatar image
swethaashwini asked

ON Clause in INNER JOIN

Hi, Could any one of you explain the below query. If you look at the bold thing, i see two ON CLAUSE, how does it work??
SELECT      
   PC.Name AS Category, PS.Name AS Subcategory, 
   DATEPART(yy, SOH.OrderDate) AS Year, 
   'Q' + DATENAME(qq, SOH.OrderDate) AS Qtr, 
   SUM(DET.UnitPrice * DET.OrderQty) AS Sales
FROM Production.ProductSubcategory PS 
INNER JOIN Sales.SalesOrderHeader SOH 
INNER JOIN Sales.SalesOrderDetail DET 
    ON SOH.SalesOrderID = DET.SalesOrderID 
INNER JOIN Production.Product P 
    ON DET.ProductID = P.ProductID 
    ON PS.ProductSubcategoryID = P.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC 
    ON PS.ProductCategoryID = PC.ProductCategoryID
WHERE (SOH.OrderDate BETWEEN (@StartDate) AND (@EndDate))
GROUP BY DATEPART(yy, SOH.OrderDate), PC.Name, PS.Name, 
   'Q' + DATENAME(qq, SOH.OrderDate), PS.ProductSubcategoryID
t-sqljoins
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Oleg avatar image
Oleg answered
The first of the two ON clauses belongs to the join with Production.Product P, while the second actually belongs to the Production.ProductSubcategory PS joined with the bunch. If you can make it look like this:
FROM Production.ProductSubcategory PS 
INNER JOIN 
(
    Sales.SalesOrderHeader SOH 
    INNER JOIN Sales.SalesOrderDetail DET 
        ON SOH.SalesOrderID = DET.SalesOrderID 
    INNER JOIN Production.Product P 
        ON DET.ProductID = P.ProductID
)
    ON PS.ProductSubcategoryID = P.ProductSubcategoryID
then it would be a bit easier to understand. Usually, the obscurities like this are caused by the tools used to generate the SQL queries instead of writing them by hand. Oleg
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I dont get the clear picture, how it is working. Is there any specific reason for using this kind of join? When and where can this be used/useful?
0 Likes 0 ·
Oleg re-wrote the query so you can easily see where the ONs are being used. Your original query made it difficult to see that SalesOrderHeader and Product were being joined together and then joined to ProductSubcategory. I would rather lean towards re-ordering the joins so that I started with SalesOrderHeader, then Product, then ProductSubcategory. The optimiser will re-order ht joins as it sees fit any way - and as they are inner joins it shouldn't affect your query results anyway.
0 Likes 0 ·
@williamD - you are right, but it will make a difference if you are using a "join order" hint, but I wouldn't recommend that anyway, at least not as long as your data will change in future.
0 Likes 0 ·
@Hakan - yes, hints can cause a query to run differently. However, the query shown here will not return different results if the join order is forced, it will just have a different execution plan and run faster/slower.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.