The SQL LEFT JOIN Includes all rows from the left table and those that match from the right. For that that don’t match from the right, NULL is returned in the corresponding columns.
This is the key difference between a LEFT JOIN and inner join. Where an inner join only returns rows matching the join condition, in a left outer join, it’s guaranteed all the left tables rows are included in the result.
This is illustrated below:
Check out the results for Blender and notice there is no matching Customer, it is NULL.
A left join is commonly called a left outer join.
The LEFT JOIN general form is:
SELECT table1.column1, table2.column2, …
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
SQL LEFT JOIN Example
Not every person in the AdventureWorks database is an employees. Let’s list every person, and some of their employment data, if it exists. We’ll use a LEFT JOIN to do so.
Keep in mind it is a SQL LEFT JOIN, as the person table, which has every row we wish to return, is on the left side of the JOIN operator.
/* List all People and any employment data, if it exists. Use Outer Join to do so... */ SELECT P.FirstName , P.LastName , E.HireDate , E.JobTitle FROM Person.Person AS P LEFT JOIN HumanResources.Employee AS E ON P.BusinessEntityID = E.BusinessEntityID;
Look at the results and notice there are NULL’s for some people. This is because there are no corresponding entries in the Employee table. The LEFT JOIN is working as advertised, return all matches, and a NULL where none exist.
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…
Additional SQL Join Resources
To learn more about the joins, check out these useful resources: