what is a left outer join in sql? 4 Practical Examples

Today’s article will discuss the LEFT OUTER JOIN in SQL. We’ll go through several examples of typical LEFT OUTER JOIN usage and compare it with INNER JOIN.

A JOIN is an SQL feature that allows you to combine data from two or more tables. The nature of relational databases makes JOIN one of the most commonly used features in SQL. There are many different types of JOINs. We’ll focus on LEFT OUTER JOIN here.

To get a comprehensive recap of different types of JOINs, try our interactive SQL JOINs course. Aside from the basics, it explains joining multiple tables and using self- and non-equi joins. You’ll complete over 90 hands-on exercises – all based on real-world examples!

If you need a refresher on JOIN concepts later on, make sure to download our SQL JOIN Cheat Sheet.

What Is a LEFT OUTER JOIN?

A LEFT JOIN, or LEFT OUTER JOIN, is a type of join that outputs all rows from the left table and the matching rows from the right table.

What’s the Difference Between LEFT OUTER JOIN and LEFT JOIN?

There’s no difference! The full name of this type of join is LEFT OUTER JOIN, but it can shortened to LEFT JOIN. SQL accepts both LEFT OUTER JOIN and LEFT JOIN with the same meaning.

As LEFT JOIN is shorter, it is used more often. When you see it, it simply means LEFT OUTER JOIN. The ‘Outer’ is implied, as there’s no other left join than a left outer join.

LEFT OUTER JOIN Syntax

The syntax of LEFT JOIN follows the standard JOIN syntax:

1. Reference the first table in FROM.
2. Use the LEFT JOIN keyword to reference the second table.
3. Use the ON keyword to specify the joining condition.

In other words, the syntax is:

SELECT …
FROM table_1
LEFT JOIN table_2
ON table_1.column = table_2.column;

So how does LEFT OUTER JOIN work? In the generic example above, the query will reference table_1 and left join it with table_2. It will first return all the rows from table_1, no matter the joining condition. Why? Because this is the nature of LEFT JOIN – it returns all the rows from the left (i.e. first) table.

Then the query will look at the joining condition – in this case, where a value in column from table_2 matches a value from column from table_1. The LEFT JOIN will return only values from the right (i.e. second table, or table_2) table where the joining condition is matched. When no such values are in the right table, the returned values will be NULL. You can see a visual example of this.

When the tables are joined, it goes without saying that you can choose any column from both tables in SELECT.

Now that you know the syntax, the only thing left is to put it into practice.

4 Examples of LEFT OUTER JOIN

First, let’s introduce the dataset.

The first table, departments, has the following data:

The second table is employees. The table has the following data:

Two employees have a NULL value in the column department_id. These are new employee records that haven’t been updated with the department yet.

Example 1: Find All Employees and Their Departments

Let’s use the above tables to list all the employees and their departments.

This is an article about LEFT OUTER JOIN, so there’s no surprise: we’ll use exactly that join to solve this problem. Here’s the query:

SELECT 
  e.id,
  e.first_name,
  e.last_name,
  d.department_name
FROM employees e 
LEFT JOIN departments d
ON e.department_id = d.id
ORDER BY e.id;

The result data we need is the employees' ID numbers, names, and departments. That’s why these columns are in SELECT.

To get all these columns, we need to access data from both tables. Let’s see how this is done.

First, the table employees is referenced in FROM. Why this table and not the other one? Remember: Which table is referenced first matters in LEFT JOIN. Why? Because that table is the left table, and LEFT JOIN will return all the rows from that table no matter what.

We reference the table employees first because we need to list all the employees from that table. Then we reference the table departments. We join both tables on the condition that the column department_id from the table employees is the same as the column id from the table departments. These two columns are shared dimensions (the primary key and foreign key) between these two tables, so they’re ideal for use in the join condition.

To have more readable output, we order the results by employee ID:

The output lists all the employees – all 15 of them. It also shows their departments, which are ‘Accounting’ and ‘Sales’. You’ll notice that some employees have NULL values in the department_name column. Two of them we mentioned earlier: new employees without an updated department ID in the table. These had NULL values in the initial employees table, and are marked in blue.

There are, however, other employees – marked in green – with NULL values. This is the result of the LEFT JOIN. All these employees have a department ID of 4, but the table departments doesn’t contain this value. It seems that the table departments might be calling for an update, too. A really shabby database for such a respectable fictive company!

Remember: The values from the left table not found in the right table will be shown as NULL. 

What If We Used an INNER JOIN?

Let’s write the above query again, this time with an INNER JOIN (usually abbreviated to just JOIN) instead of a LEFT JOIN:

SELECT 
  e.id,
  e.first_name,
  e.last_name,
  d.department_name
FROM departments d
JOIN employees e
ON e.department_id = d.id
ORDER BY e.id;

The syntax is exactly the same; we just used a different join type. Let’s see what the result will be:

Some employees are missing; there are only nine here. Closer inspection shows that the employees with IDs 3, 4, 7, 9, 11, and 12 are not included in the result. Why? If you go back, you’ll see that the missing employees are those with NULLs in department_name in the LEFT OUTER JOIN output.

Remember, INNER JOIN will return only matching rows from both tables – in other words, only those employees who have a department ID found in both tables.

So in this situation, where we wanted all employees and their departments, the LEFT JOIN is the right choice. Now we also see the employees without the department, and we can tell our database needs updating.

Example 2: List All Departments and Their Employees

To get the desired result, we have to switch the order of the tables in LEFT JOIN.

Here’s the query:

SELECT 
  d.id,
  d.department_name,
  e.first_name,
  e.last_name
FROM  departments d
LEFT JOIN employees e
ON d.id = e.department_id
ORDER BY d.id;

We tweak the order of the columns in SELECT to present the output better. Most of the columns are the same as before. This time, we’re selecting the department ID from the table departments, not the employee ID.

Now, the table departments is our left table. This is because we want to show all departments from that table, whether or not they appear in the table employees – our right table.

The ON condition stays the same; we only reversed the order of the columns. It really doesn’t matter; it could have remained in the same order. This is just for aesthetic reasons, as it’s easier to read the condition when it follows the order of the tables in LEFT JOIN.

Also, we sort the output by department ID.

Here’s the result:

This output shows only nine employees, compared to 15 in the previous example. All employees with NULL values in the last example’s output do not appear in this output.

The reason is, again, LEFT JOIN. As we made departments our left table, it listed all the departments and their employees. All the other employees are not here. Why? Either they have department ID 4, which doesn’t appear in the table departments, or they are new employees (remember them?)  with NULL as a department ID.

The only NULLs in this output appear in the last row. There’s the department ‘Compliance’, which doesn’t have any employees allocated to it. In other words, there are no employees with the value 5 in the column department_id of the employees table.

Remember: The order of the tables in LEFT JOIN matters!

What If We Used an INNER JOIN?

Let’s change the query and use an INNER JOIN:

SELECT 
  d.id,
  d.department_name,
  e.first_name,
  e.last_name
FROM departments d
INNER JOIN employees e
ON d.id = e.department_id
ORDER BY d.id;

The output is basically the same as when we inner joined Example 1. The only difference is that there is a department ID instead of an employee ID. Other than that, it’s the same, so we won’t spend time analyzing it.

Example 3: List All Departments and the Number of Employees in Each

LEFT JOIN, like all other joins, is often used with SQL’s aggregate functions. This will come in handy here.

Have a look at this query:

SELECT 
  d.department_name, 
  COUNT(e.id) AS number_of_employees
FROM departments d
LEFT JOIN employees e
ON d.id = e.department_id
GROUP BY d.department_name;

Let’s start explaining from the FROM clause. Since we want to list all the existing departments, we first refer to the table departments in FROM. Why? Because there are some employees without a department, and we don’t want them in our result. As we saw, there is also a department without employees; we want this department in the output regardless. 

Then follows the table employees after LEFT JOIN. The tables are joined on the same condition as in the previous example.

To get the correct output, we need to list the department name in SELECT. We also need to use the COUNT(e.id) aggregate function to count the number of employee IDs.

Why can’t we use COUNT(*)? Because it counts all the rows, including NULL values. This would skew our results. The compliance department has zero employees, which would have been shown as NULL when joined. COUNT(*) would have counted this NULL as one, which would not be accurate.

The COUNT(e.id) option ignores NULL values and will count only employees whose ID is not NULL. This is more of an aggregate function topic than a LEFT JOIN topic, so we won’t go into further details.

However, it is extremely important to understand when to use the various COUNT() options. You can refer to our article detailing all the different incarnations and uses of the COUNT() function for more information. 

Back to our code. After counting and joining,  the output is grouped by the department name. All that will result in the list of departments with the number of employees in each department:

The result shows there are, in total, nine employees across the company departments: six in Accounting, zero in Compliance, and three in Sales.

What If We Used an INNER JOIN?

Now, let’s do the same query but with INNER JOIN.

SELECT 
  d.department_name, 
  COUNT(e.id) AS number_of_employees
FROM departments d
JOIN employees e
ON d.id = e.department_id
GROUP BY d.department_name;

Will the result be different? Let’s see.

The result, again, shows there are nine employees across various departments. What is obviously missing is that the company has a Compliance department that’s not shown in this result.

If we had used INNER JOIN, we would have concluded that there are only two departments in the company. LEFT JOIN was, again, the right choice since there may be (at least temporarily) a department without employees.

Example 4: List All the Artists, Their Albums, and the Grammy Award Category

We’ll use three tables in this example. The first one is artists; it contains the following data about musical artists:

The next table is albums:

The third table is named grammy_award. It’s a list of Grammy award categories. It has only two categories: the best album winner and the best album nominee.

The task: List all the artists, their albums, and the Grammy Award category

LEFT JOIN can also be used to join more than two tables, and we’ll see how in the query below:

SELECT 
  artist_name,
  album_title,
  grammy_category
FROM artists ar
LEFT JOIN albums al
ON ar.id = al.artist_id
LEFT JOIN grammy_award ga
ON al.grammy_category_id = ga.id;

First, we listed all the required columns to show the artist's name, the album title, the year of release, and the Grammy Award category.

We see from the presented tables that there are albums without artist information. Our catalog is incomplete, so the safest way to list all the available artists is to LEFT JOIN tables and consider the table artists as the left table. It is left joined with the table albums on the artist ID.

To fetch the data about Grammys, we must somehow join the third table. How is this done? Simple: write the LEFT JOIN command again and reference the table grammy_award after it. This will LEFT JOIN albums with grammy_award. The tables are joined on the Grammy category ID.

Let’s see the output:

The result shows all the artists, their albums, and whether the album was a nominee or a winner for the Album of the Year Award. It also shows the albums that were neither Grammy nominees nor winners.

What If We Used INNER JOIN?

Here’s the same code as above, with JOIN instead of LEFT JOIN:

SELECT 
  artist_name,
  album_title,
  grammy_category
FROM artists ar
JOIN albums al
ON ar.id = al.artist_id
JOIN grammy_award ga
ON al.grammy_category_id = ga.id;

Let’s see what the code returns:

This result is incomplete. It’s missing one artist: George Benson. It’s also missing the albums without any Grammy category. Since we weren’t trying to list only the albums with a Grammy category, the LEFT JOIN was the right choice.

Here are some additional explanations on how to LEFT JOIN multiple tables.

Beginners’ Corner: Tips on Writing JOINs

Here’s an overview of the most common mistakes beginners make when using the LEFT OUTER JOIN in SQL.

We already covered them in the examples, but it might not be that obvious to a less experienced eye. So let’s put them into words.

Choosing the Correct Join

We compared all the LEFT JOIN examples with the INNER JOIN versions. As you saw, the output changes significantly. That’s why you need to carefully choose which join you’ll use.

The best tip is to think about those two join definitions. So, if you need only matching data from two tables, then you need INNER JOIN. In all other cases, LEFT JOIN will probably be the correct choice.

Deciding Which Table Is the Left One

Once you choose to use the LEFT JOIN, how do you know which table should be the left table? First of all, the left one is the table that comes immediately after FROM. The right one is the one that comes after LEFT JOIN.

But how do you decide which is which?  You need to correctly understand the problem you’re solving. Look for cues in the wording and logic. If you need all employees, then the table with the employees will be the left one. In other words, if you think one of your tables needs to be shown unchanged, that’s your left table.

Be Careful With Aggregate Functions

Even if you make a mistake and choose the wrong join, it’s very likely that you’ll catch it in the output if you don’t aggregate the data. If you’re familiar with your data, you’ll see some rows missing or no NULLs where you expected them to be.

However, be extra careful when using aggregate functions. As the output will be aggregated, it will be much more difficult to see the error from the output – unless you know all your data aggregation results by heart, which is highly unlikely. You wouldn’t need SQL in that case, would you?

This is especially true when using COUNT(). As you saw in one of our examples, COUNT(*) can give you different results from COUNT(column_name). It’s extremely important that you know what you want to achieve and how COUNT() works.

Remember: COUNT(column_name) ignores NULL values, while COUNT(*) doesn’t!

So when working with aggregate functions, test your query with INNER JOIN – if you have the opportunity – and see if it returns different results. If it doesn’t, then it doesn’t matter which join you use.  If it does, go back to your problem definition and see which join logic better suits  your business logic.

Cascading LEFT JOINs

This is observed when joining more than two tables. If you choose to LEFT JOIN the first two tables, you usually have to LEFT JOIN all the other tables.

You saw that in our last example, as we used two LEFT JOINs. Even if we used a LEFT JOIN first and then an INNER JOIN, we wouldn’t get the list of all the artists and the other data. Including INNER JOIN in this chain of joins will give the same result as using INNER JOIN instead of each LEFT JOIN.

This is not always the case. But it’s quite a safe bet to follow this rule of thumb: if you need one LEFT JOIN, you need all LEFT JOINs.

Looking For More LEFT JOIN Practice?

This article gave you a taste of what a LEFT JOIN is and how to manage its occasional trickiness. We also practiced writing code as you got familiar with the LEFT JOIN syntax.

To master all the nuances of LEFT OUTER JOIN in SQL, we recommend more practice. Four examples are not enough! To really build on what you learned here, we recommend our SQL JOINs course. Apart from theoretical foundations, you’ll also get plenty of hands-on practice with real-world scenarios.

If you need a refresher on JOIN concepts later on, make sure to download our SQL JOIN Cheat Sheet.

Happy learning!