This post gives an example of using left joins on a series of tables in the Itzik Ben-Gan et al database that was used in the Microsoft Press book called T-SQL Querying published in 2015.

For a more basic example of a left join, have a look at the next post in this series.

Have a look at the database diagram of most of the tables in the database at the post Itzik Ben-Gan’s Database.

select E.empid, O.orderid, D.productid, D.qty, P.productname, C.categoryname
from HR.Employees E
left join Sales.Orders O
on E.empid = O.empid
left join Sales.OrderDetails D
on O.orderid = D.orderid
left join Production.Products P
on D.productid = P.productid
left join Production.Categories C
on P.categoryid = C.categoryid
order by empid, orderid, productid

Here are the results of the first few rows in a screenshot of SSMS. The results produce 2155 rows.

• Each Employee can have zero, one or many Orders
• Each Order can have one or many Products

If we change all of the left joins in the above query to inner joins and re-run it, how many rows will be returned? It turns out that the same number of rows are returned, namely 2155 rows. What does this mean? Does it mean that ALL employees in the database have at least one Order?

Here is a diagram of the different types of joins from http://sql.sh/2401-sql-join-infographie.