Get to know the details of LEFT JOIN, one of the most common SQL JOIN types.
SQL is mainly about getting data from databases. Very often, you’ll need data to be combined from multiple tables in your database. That’s when JOINs come into play.
LEFT JOIN is one of the JOIN types that you are likely to use very often. In this article, I’ll explain the syntax of LEFT JOIN and how it works using several examples and illustrations.
In SQL, you use JOIN to combine data from two tables based on a column with matching values. For example, if you have a table with customer information (customer ID, first name, last name, etc.) and another table with the order data for a specific period (order ID, date, customer ID, product ID, etc.) you can join these two tables based on the columns that store customer ID numbers. This will give you order and customer details for each customer.
However, it’s important to know that there are different types of JOINs in SQL. You’ll need to choose the right one based on the rows you want to keep in the result set. If you’re interested in customers that have only placed orders during a specific time period, you’ll use a simple JOIN (also called INNER JOIN). This type of SQL JOIN keeps only those rows that are present in both tables; in our case, you’ll see a row in the result set only if the customer’s ID is in the customers table as well as the orders table.
There are cases when you want to keep rows from the first table that don’t have the corresponding records in the second table. We may want to see information about all customers in our result set, even if they had no orders in a specific time period. In this case, you’ll use a LEFT JOIN, which combines data from two tables so that all rows from the left (first) table are included in the result.
This is the JOIN type that we’ll focus on in this article. If you are not sure which JOIN type you need in a particular case, check out our video tutorial on SQL LEFT, RIGHT, and FULL JOINs.
LEFT JOIN Explained
LEFT JOIN, also called LEFT OUTER JOIN, returns all records from the left (first) table and the matched records from the right (second) table. If there is no match for a specific record, you’ll get NULLs in the corresponding columns of the right table. Let’s see how it works with the customers and orders example mentioned above.
Here are our customers and orders tables.
| id | first_name | last_name |
| 1 | Steve | Brown |
| 2 | Helen | Stewart |
| 3 | Jane | Smith |
| 4 | Jack | Bond |
| id | date | customer_id | product_id | amount |
| 1 | 2020-12-20 | 3 | 23 | 213.99 |
| 2 | 2020-12-20 | 3 | 12 | 412.45 |
| 3 | 2020-12-21 | 1 | 18 | 89.95 |
| 4 | 2020-12-22 | 4 | 8 | 54.60 |
To join these two tables while keeping all the records of the customers table, you can use the following SQL query:
SELECT * FROM customers LEFT JOIN orders ON customers.id = orders.customer_id ORDER BY customers.id;Let’s go through the syntax of LEFT JOIN:
- SELECT – Start by listing the columns (from both tables) that you want to see in the result set (here we select all columns using *);
- FROM – Put the name of the left table, the one where you want to keep all the records (i.e. customers);
- LEFT JOIN – Write the name of the second (right) table (i.e. orders);
- ON – Use this keyword to indicate the columns that will be used to join the tables, i.e. the ones with the matching values. (Here, it’s id from customers and customer_id from orders).
In this query, we also use ORDER BY to order the output by customer’s ID, but this is optional.
After running this query, you’ll get the following table.
| id | first_name | last_name | id | date | customer_id | product_id | amount |
| 1 | Steve | Brown | 1 | 2020-12-21 | 1 | 18 | 89.95 |
| 2 | Helen | Stewart | [NULL] | [NULL] | [NULL] | [NULL] | [NULL] |
| 3 | Jane | Smith | 3 | 2020-12-20 | 3 | 23 | 213.99 |
| 3 | Jane | Smith | 3 | 2020-12-20 | 3 | 12 | 412.45 |
| 4 | Jack | Bond | 4 | 2020-12-22 | 4 | 8 | 54.60 |
Take a look at the animation:
How does LEFT JOIN work?
First of all, the database looks into each row of the left table and searches for a match in the right table based on the related columns. If there is a match, it adds data from the right table to the corresponding row of the left table. If there are several matches (like in our case with customer #3), it duplicates the row in the left table to include all records from the right table. If there is no match, it still keeps the row from the left table and puts NULL in the corresponding columns of the right table (customer #2 in our example).
Simple, isn’t it? You can practice LEFT JOINs in this interactive SQL JOINs course. And now let’s look into some more examples of working with LEFT JOINs.
SQL LEFT JOINs In Use
Example with employees
We have a table with employees info and want to match it with the table that lists all the bonuses paid in January (jan_bonuses).
| id | first_name | last_name |
| 1 | Joe | Biden |
| 2 | Bernie | Sanders |
| 3 | Peter | Buttigieg |
| 4 | Elizabeth | Warren |
| 5 | Michael | Bloomberg |
| id | employee_id | amount |
| 101 | 1 | 3460.00 |
| 102 | 2 | 2340.00 |
| 103 | 4 | 1800.00 |
We want to join these tables so we can see who received bonuses in January. Our result should include all employees, no matter if they received a bonus or not. Thus, we are going to use a LEFT JOIN:
SELECT * FROM employees LEFT JOIN jan_bonuses ON employees.id = jan_bonuses.employee_id;| id | first_name | last_name | id | employee_id | amount |
| 1 | Joe | Biden | 101 | 1 | 3460.00 |
| 2 | Bernie | Sanders | 102 | 2 | 2340.00 |
| 3 | Peter | Buttigieg | [NULL] | [NULL] | [NULL] |
| 4 | Elizabeth | Warren | 103 | 4 | 1800.00 |
| 5 | Michael | Bloomberg | [NULL] | [NULL] | [NULL] |
As expected, the result includes all employees. If an employee is not found in the table with bonus info, the corresponding columns from the second table are filled in with NULL values.
Interested to know how other OUTER JOINs work? Read this illustrated guide to the SQL OUTER JOIN to learn how RIGHT OUTER JOIN and FULL OUTER JOIN differ from LEFT OUTER JOIN.
Example with countries
In our final example, we’ll join two tables using LEFT JOIN but we’ll include only selected columns in the final result.
We have a list of countries with some basic information and we want to supplement it with the GDP data for 2019, where such is available.
| id | name | population | currency | area |
| 201 | United States | 330,770,244 | USD | 9,525,067 |
| 202 | China | 1,405,631,120 | CNY | 9,596,961 |
| 203 | Japan | 125,770,000 | JPY | 377,975 |
| 204 | Germany | 83,122,889 | EUR | 357,114 |
| 205 | India | 1,370,521,240 | INR | 3,287,263 |
| rank | country_id | gdp_nominal_MUSD |
| 1 | 201 | 21,427,700 |
| 2 | 202 | 14,342,903 |
| 3 | 203 | 5,081,770 |
In our output, we don’t need all the information on each country – only the country’s ID, name, population, and nominal GDP for 2019. Here’s the query we’ll use:
| id | name | population | gdp_nominal_MUSD |
| 201 | United States | 330,770,244 | 21,427,700 |
| 202 | China | 1,405,631,120 | 14,342,903 |
| 203 | Japan | 125,770,000 | 5,081,770 |
| 204 | Germany | 83,122,889 | [NULL] |
| 205 | India | 1,370,521,240 | [NULL] |
The table includes three columns from the countries table and one column from the gdp_2019 table. As expected, LEFT JOIN keeps all records from the first table and inputs NULL values for the unmatched records.
Note that in this SELECT statement, we have simply listed the names of the columns we want to see in the result. This works only when we don’t have columns with the same name in both tables. The safer approach is to specify the table for each column we request, i.e. countries.id, countries.name, countries.population, gdp_2019.gdp_nominal_MUSD.
If you need to LEFT JOIN more than two tables, read my guide on left-joining multiple tables in SQL.
Time to Practice LEFT JOIN!
You’ve learned the syntax of LEFT JOIN and know when and how to use this SQL JOIN type. You are ready for the next step – writing your own SQL queries with LEFT JOINs.
I recommend starting with our SQL JOINs course, which includes 93 interactive exercises covering INNER JOIN, OUTER JOINs, non-equi JOINs, and more. After finishing this course, you’ll know how to choose the correct type of JOIN, how to join multiple tables, how to join a table with itself, and how to join tables using conditional operators other than equality.
Do you feel unsure about practicing SQL JOINs online? Check out this article that introduces the best approaches to practicing different types of SQL JOINs.
Thanks for reading, and happy learning!