ON Clause in INNER JOIN

Question

question

swethaashwini asked Dec 14, '10

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-sql joins

10 |1200

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

Answer 1 · 2010-12-14T13:29:09Z

Oleg answered Dec 14, '10

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

10 |1200

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

swethaashwini commented · Dec 14, 2010 at 04:15 PM

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 ·

WilliamD commented · Dec 14, 2010 at 11:55 PM

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 ·

Håkan Winther commented · Dec 15, 2010 at 12:22 AM

@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 ·

WilliamD commented · Dec 15, 2010 at 12:53 AM

@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 ·

question