Do You Want to Learn SQL?

Take our free eight day course, where I'll teach you in simple to understand English all you need to get started learning SQL.


Free Beginners SQL Course!

SQL Left Join



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:


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 


  • 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


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,
FROM   person.Person
      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
         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

More from the blog

MySQL PostgreSQL SQLite SQL Server