Multi-Join Queries

So far we have just joined two tables together. Now we will work with four tables, based on Itzik’s database. Below is a screenshot of an Excel file showing the four tables and their relations (at least the ones we will be paying attention to). An employee can take many orders. An order may have one or many detail lines. Each unique product can be in many orders.

select Employees.empid, Orders.orderid, Products.productid, OrderDetails.UnitPrice, Products.productname
from [dbo].[Employees]
left join [dbo].[Orders] on Employees.[empid] = Orders.empid
left join [dbo].[OrderDetails] on Orders.orderid = OrderDetails.orderid
left join [dbo].[Products] on OrderDetails.productid = Products.productid
order by empid, Orders.orderid, Products.productid

Below is a screenshot of the above query’s results in SSMS. Here we are using all left joins.

select Employees.empid, Orders.orderid, Products.productid, OrderDetails.UnitPrice, Products.productname
from [dbo].[Employees]
left join [dbo].[Orders] on Employees.[empid] = Orders.empid
left join [dbo].[OrderDetails] on Orders.orderid = OrderDetails.orderid
inner join [dbo].[Products] on OrderDetails.productid = Products.productid
order by empid, Orders.orderid, Products.productid

Below is the screenshot of the results of the above query. Here we are using an inner join to join the Products table. Notice that employee id 18, Bob Lock is no longer in the results.

What if we change the query and put a right join at the end of it?

select Employees.empid, Orders.orderid, Products.productid, OrderDetails.UnitPrice, Products.productname
from [dbo].[Employees]
left join [dbo].[Orders] on Employees.[empid] = Orders.empid
left join [dbo].[OrderDetails] on Orders.orderid = OrderDetails.orderid
right join [dbo].[Products] on OrderDetails.productid = Products.productid
order by empid, Orders.orderid, Products.productid

Which Employees are Pushing Which Suppliers?

You want a query that shows the Employee-Supplier pairs. We want to include ALL of the employees in the results, even if they had no orders. We don’t care about products that the employees never included in any orders. We don’t care about any suppliers that we never used. For this reason we want to use inner joins on the Order Details, Products and Suppliers. We need to combine a left outer join to the results of a bunch of inner joins. How do we do that?

We have added a Supplier table to the mix. Suppliers offer products for us to purchase and resell. Each of the three queries produce the same results. We use DISTINCT in all three because we want to remove any duplicates. The temptation is to use the first query but it is not the best one to use. The first one is not optimized.

-- what suppliers are our employees ordering from?
select distinct Employees.empid, Suppliers.companyname
from [dbo].[Employees]
left join [dbo].[Orders] on Employees.[empid] = Orders.empid
left join [dbo].[OrderDetails] on Orders.orderid = OrderDetails.orderid
left join [dbo].[Products] on OrderDetails.productid = Products.productid
left join [dbo].[Suppliers] on Products.supplierid = Suppliers.supplierid
order by Employees.empid, Suppliers.companyname
-- the query above does produce the correct results but you did not really
-- mean to preserve these rows with these left out joins.
-- The query below is a more accurate solution.
select distinct Employees.empid, Suppliers.companyname
from [dbo].[Employees]
left join 
(
[dbo].[Orders] 
inner join [dbo].[OrderDetails] on Orders.orderid = OrderDetails.orderid
inner join [dbo].[Products] on OrderDetails.productid = Products.productid
inner join [dbo].[Suppliers] on Products.supplierid = Suppliers.supplierid
)
on Employees.[empid] = Orders.empid
order by Employees.empid, Suppliers.companyname
-- The query below is the same as the query above except that it is 
-- rewritten in a different way that is still valid.
select distinct Employees.empid, Suppliers.companyname
from [dbo].[Employees]
left join [dbo].[Orders] 
inner join [dbo].[OrderDetails] 
inner join [dbo].[Products] 
inner join [dbo].[Suppliers] 
on Products.supplierid = Suppliers.supplierid
on OrderDetails.productid = Products.productid
on Orders.orderid = OrderDetails.orderid
on Employees.empid = Orders.empid
order by Employees.empid, Suppliers.companyname

Note that if you are planning to use the third format, the order of the ON clauses matters. Below are the results in SSMS.

Below is a diagram in Excel of the tables and relationships used in the last two queries in the above set of three queries.