SQL Left Join Tutorial


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:

SQL LEFT JOIN example

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, …
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 

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;
/*
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. 

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…

Additional SQL Join Resources 

To learn more about the joins, check out these useful resources: 

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