SQL Left Join: A Complete Guide

An SQL LEFT JOIN is a type of SQL join. This join returns all the tables from a specified “left” column and the corresponding rows that match a particular condition in the “right” column.

In many cases, when you’re writing a query in SQL, you’ll only need to focus on a single table. However, there are many situations where you’ll need to query two or more tables at the same time. In this situation, you will want to create a result that combines information from both tables.

In SQL, this is referred to as a join. Joins allow you to get information from multiple tables and combine the result into a joined table. For example, you can join employees and departments tables together to get the department name for each employee.

In this tutorial, we are going to break down one type of join in SQL: the LEFT JOIN. We’ll discuss where LEFT JOINs are useful, and how you can use them in your SQL queries.

SQL LEFT JOIN

The SQL LEFT JOIN returns all records from a “left” table, specified in your query. It matches values from this table based on your conditions to a “right” table. Any matches are returned in addition to all records from the “left” table. _LEFT JOIN_s are a type of SQL outer join.

For example, you could use a left join to get a list of the department name for each employee. This would let you see all employees even if they don’t have a department assigned.

The basic syntax for a LEFT JOIN operation is as follows:

SELECT Table1.Column1, Table2.Column1
FROM Table1
LEFT JOIN Table2
ON Table1.Column2 = Table1.Column2;

We start our join with an SQL SELECT statement. The table in the FROM statement is our “left” table. Then, we use the LEFT JOIN keyword. This lets us specify a “right” table.

We use the ON statement to determine the condition on which our tables are connected. LEFT JOINS are a type of outer join. Thus, you may see this type of join referred to as left outer joins.

Let’s use an example to illustrate LEFT JOINs work.

LEFT JOIN SQL Example

Say that we want to get a list of the department names where each employee works. We want to retrieve this information using one query. Here is a query that would allow us to get this data:

SELECT employees.name, employees.title, company_departments.name AS DeptName
FROM employees
LEFT JOIN company_departments
ON employees.department_id = company_departments.department_id;

Our query returns seven records:

(7 rows)

On the first line of our query, we specify that we want to get three columns. We retrieve the name of our employees, their titles, and the name of the department for which they work. The AS DeptName tells our query that the department name column should be called DeptName.

On the next line, we specify that we want to get information from our employees table. Then, we create a LEFT JOIN between that table and company_departments. On the final line, we link our two tables together using their common values: department_id.

LEFT JOINs only return all rows from the left table and rows from the right table for which the join condition is met.

If we have an employee with a department ID of 9 and that department does not exist, they will still appear in our query. If we have a company department that does not exist, it will not appear in our JOIN query.

Let’s run the query again, but this time with a new employee in our database. These are the values for our new employee:

(1 row)

The department ID 9 does not exist within our table company_departments. But when we run the same left join query as above, Adam’s record is still included, because we are using a LEFT JOIN. Here is the result of the same INNER JOIN query we executed above, but with Adam’s record:

Conclusion

SQL LEFT JOINs query more than one table and return all rows from a specified “left” table. They also return rows from the right table where the JOIN condition is met.

For example, say you want to return a list of customers as well as the names of the loyalty plans they subscribe to. If the loyalty plan names are in a different table, you could use a left join to retrieve them.

We’ve written a How to Learn SQL guide to help learners advance their knowledge of SQL. The guide contains a list of top learning resources and some expert advice to help you on your way. Check out the guide on our How to Learn SQL page.