In order to more easily understand left joins we will modify Itzik Ben-Gan’s database from the previous post and reduce the number of tables, columns and rows in our example database. Now we can more easily see the results when you compare a left join to an inner join.

What is a left join? A left join is a type of outer join. A right join is also a type of outer join.

Simplified Sample Database

Below is what the two tables, Employees and Orders look like when you run a SELECT statement on each. Notice that one of the employees has not yet taken any orders. Bob Lock, employee 18 is not to be found in the Order table in the empid column. Also notice that all of the orders have a valid employee assigned to them. There is no order in the Orders table that does not have a corresponding employee is the employees table.

Left Outer Join

We have a one-to-many relationship here. One employee can have many (or even none) orders. Our left join will show ALL of the rows in the Employees table, even if there are no matching rows in the orders table. The key words left and left outer are equivalent. We could have used left outer instead of just left and the results would be the same. The Employees table is the referenced table and the Orders table is the referencing table.

select Employees.empid, Employees.FirstName, Employees.LastName, Orders.orderid
from [dbo].[Employees]
left join [dbo].[Orders]
on Employees.[empid] = Orders.empid

Here are the results in the SSMS. Notice the Bob Lock row with the NULL result. The Bob Lock row is known as an outer row.

Inner Join

Let’s run an inner join on the same to tables. The only difference in the SQL code is that we changed left to inner. Form a logical query-processing perspective, an inner join involves two steps. It starts with a Cartesian product between the two input tables, like a cross join. It then applies a filter that usually involves matching elements from both sides. In the SQL code below, we could have just specified join instead of inner join because inner is the default.

select Employees.empid, Employees.FirstName, Employees.LastName, Orders.orderid
from [dbo].[Employees]
inner join [dbo].[Orders]
on Employees.[empid] = Orders.empid

Below are the results of our above query in SSMS.

Finding Outer Rows

Which employees didn’t take any orders? We can write a query that shows us all of the outer rows. The outer rows are the ones that have no matching rows in the other table. We can use a where clause at the end of the left join query to show us all of the outer rows, if there are any. In our example we have one outer row, the Bob Lock employee.

select Employees.empid, Employees.FirstName, Employees.LastName, Orders.orderid
from [dbo].[Employees]
left join [dbo].[Orders]
on Employees.[empid] = Orders.empid
where orderid is null

Below are the results of the above query as shown in SQL Server Management Studio (SSMS).