Order of inner join

Question

I was inspecting Northwind database. There is one View called 'Invoice' fairly complicated.

I tried to change the original sql (pull last inner join up, merge with the other inner join statement).

'Official' code to get invoice: (correct)

SELECT *
 FROM           dbo.Shippers        as shipper
 INNER JOIN     dbo.Products        as product
 INNER JOIN     dbo.Employees  as employee
 INNER JOIN     dbo.Customers  as customer
 INNER JOIN     orders         as orders
 ON customer.CustomerID = orders.CustomerID 
 ON employee.EmployeeID = orders.EmployeeID 
 INNER JOIN     dbo.[Order Details] 
 ON orders.OrderID = dbo.[Order Details].OrderID 
 ON product.ProductID = dbo.[Order Details].ProductID 
 ON shipper.ShipperID = orders.ShipVia

My first try: (doesn't work)

SELECT *
     FROM           dbo.Shippers        as shipper
     INNER JOIN     dbo.Products        as product
     INNER JOIN     dbo.Employees  as employee
     INNER JOIN     dbo.Customers  as customer
     INNER JOIN     orders         as orders
     INNER JOIN     dbo.[Order Details] 
     ON orders.OrderID = dbo.[Order Details].OrderID 
     ON product.ProductID = dbo.[Order Details].ProductID 
     ON shipper.ShipperID = orders.ShipVia
     ON customer.CustomerID = orders.CustomerID 
     ON employee.EmployeeID = orders.EmployeeID 

My second try (works):

select      *
from Orders as orders
    inner join Shippers as ships
        on ships.ShipperID = orders.ShipVia
    inner join [Order Details] as ods
        on ods.OrderID = orders.OrderID 
    inner join Products as products
        on ods.ProductID = products.ProductID
    inner join Customers as customers
        on customers.CustomerID = orders.CustomerID

Both of them return 2155 lines of record.

Here is a reference for Table Structure:

My question is why 'my first try' is not right ? Also, do you think my second try is right ?

Thanks

Answer 1

Each ON clause is used to specify the join conditions for the most immediate preceding JOIN clause that hasn't yet had an ON specified.

So, indenting to show how they're matching up:

 SELECT *
 FROM           dbo.Shippers        as shipper
 INNER JOIN     dbo.Products        as product
     INNER JOIN     dbo.Employees  as employee
         INNER JOIN     dbo.Customers  as customer
             INNER JOIN     orders         as orders
                 INNER JOIN     dbo.[Order Details] 
                 ON orders.OrderID = dbo.[Order Details].OrderID 
             ON product.ProductID = dbo.[Order Details].ProductID
         ON shipper.ShipperID = orders.ShipVia
     ON customer.CustomerID = orders.CustomerID 
 ON employee.EmployeeID = orders.EmployeeID 

And the most deeply nested JOIN will be performed first. So, whilst the inner most join seems to be correct (joining orders to Order Details with an ON clause of orders.OrderID = dbo.[Order Details].OrderID), the next one out is incorrect - we're trying to join customer to the result of the previous join (or orders and order details), but with an ON clause of product.ProductID = dbo.[Order Details].ProductID - and that's wrong because we've not joined to the product table yet.

You might try re-arranging them as:

 SELECT *
 FROM           dbo.Shippers        as shipper
 INNER JOIN     dbo.Products        as product
     INNER JOIN     dbo.Employees  as employee
         INNER JOIN     dbo.Customers  as customer
             INNER JOIN     orders         as orders
                 INNER JOIN     dbo.[Order Details] 
                 ON orders.OrderID = dbo.[Order Details].OrderID 
             ON customer.CustomerID = orders.CustomerID 
         ON employee.EmployeeID = orders.EmployeeID
     ON product.ProductID = dbo.[Order Details].ProductID
 ON shipper.ShipperID = orders.ShipVia

Where now at least each ON clause is dealing in terms of table aliases that are in scope for each join.

However, I'd usually recommend (except for complex joins) to follow a pattern of:

FROM a
INNER JOIN b
    ON a.column = b.column
INNER JOIN c
    ON a_or_b.column = c.column
...

Where each ON clause is kept close to the JOIN clause that it's actually specifying the conditions for, much like your second attempt. I can see no reason for trying to get all of the JOINs to occur at the top of the FROM.

Answer 2

in your 1st try, you didn't link Shipper to Order table.

SELECT *
     FROM           dbo.Shippers        as shipper
     INNER JOIN     dbo.Products        as product
     INNER JOIN     dbo.Employees  as employee
     INNER JOIN     dbo.Customers  as customer
     INNER JOIN     orders         as orders
     INNER JOIN     dbo.[Order Details] 
     ON orders.OrderID = dbo.[Order Details].OrderID 
     ON product.ProductID = dbo.[Order Details].ProductID 
     ON shipper.ShipperID = orders.ShipVia
     ON customer.CustomerID = orders.CustomerID 
     ON employee.EmployeeID = orders.EmployeeID 

2ndly when you use join, you have to mention the columns of the two table on which the join will be done.

so when you join x and join y then you have to mention column of x and column of y. otherwise it will be a Cartesian product. when you make a join just immediately you have to mention the column.

in your second query, the ideal practice is link all the associated table 1st. you can re-write it like:

select      *
from Orders as orders
    inner join Shippers as ships
        on orders.ShipVia= ships.ShipperID  
    inner join Customers as customers
        on orders.CustomerID = customers.CustomerID
    inner join [Order Details] as ods
        on orders.OrderID  = ods.OrderID

    inner join Products as products
        on ods.ProductID = products.ProductID