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
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.
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
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.”
