November 8, 2021

The SQL LEFT JOIN Includes all rows from the left table and those that match from the right table. When the right table doesn’t match the join condition, the query returns null values for those columns.

This is the key difference between a LEFT JOIN and inner join.  Where an inner join only returns rows matching the join condition, a SQL left join returns all rows from the left table and matching columns, if possible, from the right table.

You can see this below:

SQL LEFT JOIN example

Check out the results for Blender and notice there is no matching Customer, it is NULL

You can use LEFT JOIN and LEFT OUTER JOIN interchangeably, but most people tend to use LEFT JOIN when they write their queries.

The LEFT JOIN general form is: 
SELECT table1.column1, table2.column2, …
FROM table1 
LEFT JOIN table2 ON table1.commonColumn = table2.commonColumn 

Notes: 

  • In this example table1 is the left table and table2 the right. 
  • If there is no match between the commonColumn values, table2.column2 returns NULL 

Read More! SQL Joins – The Ultimate Guide

SQL LEFT JOIN Example 

Not every person in the AdventureWorks database is an employee.  Let’s list every person, and some of their employment data, if it exists. 

Check out the following data model.  In this model, there is 1 Person to 0 or 1 Employees.

Datamodel Used for Outer Join

To construct a list of all Person LastNames, yet also show JobTitle if the Person is an Employee, we need a way of joining the two tables and include Person rows in the result, even if they don’t match Employee.

Based on what we learned above, we’ll use a SQL left join. That way we’ll return every Person, and when they match, their corresponding Employee data. The basic syntax for a left outer join is:

SELECT columnlist
FROM   table
LEFT OUTER JOIN othertable ON join condition

The SQL for the join in the diagram above is:

SELECT person.Person.BusinessEntityID,
       Person.Person.LastName,
       HumanResources.Employee.NationalIDNumber,
       HumanResources.Employee.JobTitle
FROM   person.Person
LEFT JOIN
      HumanResources.Employee
      ON person.BusinessEntityID = Employee.BusinessEntityID

Here are the first results from the query

Left Outer Join Results

Do you see the NULL’s in the second row? This is because there are no employees matching BusinessEntityID 293.Important Points: 

When working with SQL LEFT JOINS keep in mind your match from one table to another may match multiple rows.  Meaning, your result may have more rows in the result that you have in either table. 

When columns don’t match, the query the left row, and NULL for the right table columns…

Uses for Left Joins

Because outer joins not only the matching rows but also those that don’t, they are a really good way to find missing entries in tables.  This are great when you need to do diagnosis on your database to determine if there are data integrity issues.

For example suppose we were concerned that we may have some ProductSubcategory entries that don’t match to Categories.  We could test by running the following SQL

SELECT   PSC.Name AS Subcategory
FROM     Production.ProductCategory AS PSC
LEFT JOIN
         Production.ProductSubcategory AS PC
         ON PC.ProductCategoryID = PSC.ProductCategoryID
WHERE PSC.ProductCategoryID is NULL

The outer join returns the unmatched row values as NULL values.  The where clause filters on the non-null values, leaving only nonmatching Subcategory names for us to review.

Outer joins can also be used to ask questions such as:

“What sales persons have never made a sale?”

“What products aren’t assigned to a product model?”

“Which departments don’t have any assigned employees?”

“List all sales territories not assigned sales people.”

Additional SQL Join Resources

About the author 

Kris Wenzel

Kris Wenzel has been working with databases over the past 30 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

But it doesn't have to be this way.

I'm Putting together a free email course to help you get started learning SQL Server.

Name*
Email*
>