How to LEFT JOIN Multiple Tables in SQL

Can you LEFT JOIN three tables in SQL? Yes, indeed! You can use multiple LEFT JOINs in one query if needed for your analysis. In this article, I will go through some examples to demonstrate how to LEFT JOIN multiple tables in SQL and how to avoid some common pitfalls when doing so.

The best way to practice different types of joins is our interactive SQL JOINs course. It contains over 90 hands-on exercises that cover simple 2-table joins, joining multiple tables, LEFT JOIN, FULL JOIN, and many more. It is the most complete practical review of SQL JOINs available on the Internet.

What Is a LEFT JOIN?

Let’s start by reminding ourselves what a LEFT JOIN is and how to use it. You might remember that an INNER JOIN returns only the records that are in both tables. In contrast, a LEFT JOIN in SQL returns all records (or rows) from the left table and only the matched records (or rows) from the right. This implies that, if a specific row is present in the left table but not in the right, the result will include this row but with a NULL value in each column from the right. If a record from the right table is not in the left, it will not be included in the result.

The general syntax for a LEFT JOIN is as follows:

SELECT column names
FROM table1
LEFT JOIN table2
  ON table1.common_column = table2.common_column;

If you want more information on SQL joins, check out this comprehensive guide.

The LEFT JOIN is frequently used for analytical tasks. First, it is very useful for identifying records in a given table that do not have any matching records in another. In this case, you can add a WHERE clause to the query to select, from the result of the join, the rows with NULL values in all of the columns from the second table. However, first make sure that your second table doesn’t have records with NULL values in all columns except for the common field used for joining. Alternatively, use a column from the second table that is fully populated with no NULL values, if there is one.

Here is another scenario. Imagine that we run an online bookstore, and we want to compare the customers with orders in the last 6 months with those who were inactive during the same period. In this case, we want the result to include ALL customers–both active and inactive–by joining the data about our customers with the data about recent sales. This is a perfect use for a LEFT JOIN.

Here are the customers and sales tables from our bookstore.

Customers

Sales

To join these two tables and obtain the information we need for analysis, use the following SQL query:

SELECT c.id, c.first_name, c.last_name, c.gender, c.age, c.customer_since,    
       s.date AS sales_date, sum(s.amount) AS total_spent
FROM customers c
LEFT JOIN sales s
ON c.id = s.customer_id
GROUP BY c.id;

With this, we keep all the records about the customers, append the sales date, and calculate the total amount spent. In the table below, the rows that would be present in INNER JOIN are highlighted in blue, while the yellow denotes additional records due to the LEFT JOIN.

As you can see, the customers with no purchases in the given period have a NULL value for the sales date and the total amount spent, since their records are not present in the sales table. This is how a LEFT JOIN works. You may want to practice LEFT JOINs and other types of joins in our comprehensive SQL JOINs course or check out our SQL Practice track with multiple SQL exercises to practice various SQL constructions.

Now, let’s dive into more complicated cases!

Multiple LEFT JOINs in One Query

Sometimes you need to LEFT JOIN more than two tables to get the data required for specific analyses. Fortunately, the LEFT JOIN keyword can be used with multiple tables in SQL.

Let’s look at an example. We want to analyze how our recent promotional campaign has impacted the behavior of our customers.

Promotions

To do this, we need to combine the data about customers, sales, and promotions.

SELECT c.id, c.first_name, c.last_name, c.gender, c.age, c.customer_since, 
   s.date AS sale, p.date AS promotion
FROM customers c
LEFT JOIN sales s
ON c.id = s.customer_id
LEFT JOIN promotions p
ON c.id = p.customer_id;

Here is the result of the join:

As you can see, by using a LEFT JOIN, we kept the records on all of our customers, regardless of purchase history or participation in the promotional campaigns. For example, Customer 1 is in the result of the join even though he has not made any purchases nor received the promotional message. We also have Customer 4 who has purchased a book but has not received any promotional messages, as well as Customer 5 who has received a promotional message but has not made any purchases. Finally, customers who have made purchases and received promotional messages (Customers 2 and 3) are also included in the result.

Note that in this example, we used a common field from the first table to join both the second and the third tables. However, this is not always the case. Let’s look at a case in which a common field from the second table is used to left-join with the third but not with the first.

We want to explore what book genres most interest our customers. This information is very valuable, as it helps us provide a more personalized experience to our customers with specific book recommendations. For this analysis, we will need the data from customers, sales, and books. We have already joined the first two in our first example; to this, we will add the books table.

Books

To get the data needed to analyze the books and genres our customers prefer, use the following query:

SELECT c.id, c.first_name, c.last_name, s.date AS sale, 
 b.name AS book, b.genre
FROM customers c
LEFT JOIN sales s
ON c.id = s.customer_id
LEFT JOIN books b
ON s.book_id = b.id;

Here is the result of the join, in which the customer data is combined with the data about the books purchased recently (if any).

We have two customers (1 and 5) who have not purchased anything and thus have no corresponding records in the sales table. However, these rows are kept by using a LEFT JOIN. Also, the table includes the row corresponding to Customer 4 who recently purchased a book not in our database and therefore has no data from the books table. The result of the join still includes this record because of the LEFT JOIN.

As you can see, the LEFT JOIN in SQL can be used with multiple tables. However, to make sure you get the expected results, be aware of the issues that may arise when joining more than two tables.

Things to Consider With Multiple LEFT JOINs

Joining multiple tables in SQL can be tricky. Here are some considerations when using LEFT JOINs, especially with multiple tables.

In contrast to the INNER JOIN, the order of the tables plays an important role in the LEFT JOIN, and the results may be completely different if the order changes in your SQL query. When determining the order of tables in a LEFT JOIN, the general rule is to start with the table from which you want to keep all the records in the final result.

Also, keep in mind that a LEFT JOIN cascades to all joins in a query. If you use a LEFT JOIN, often the subsequent tables should also be left-joined. An INNER JOIN will drop records not found on both sides of the join, and you might lose all the records you want to keep--the reason for using a LEFT JOIN instead of the usual INNER JOIN in the first place.

Furthermore, a LEFT JOIN should be used for the third table when the first table has records not found in the second (common for LEFT JOINs!) and a different common field is used to join the second and the third tables. If you use an INNER JOIN in this situation, you will drop all the records from the first table not matched to the second and third tables.

This is the case with our example above, in which we joined the customers, sales, and books tables. Let’s see what happens if we use an INNER JOIN instead of a LEFT JOIN to append the data from the books table:

SELECT c.id, c.first_name, c.last_name, s.date AS sale, 
 b.name AS book, b.genre
FROM customers c
LEFT JOIN sales s
ON c.id = s.customer_id
INNER JOIN books b
ON s.book_id = b.id;

As you can see, we now get only two records instead of five. When we joined using an INNER JOIN, we lost a record corresponding to the purchase of the book not in the books table. We also lost the information about the two customers with no recent purchases--the records we needed to keep by left-joining the sales table. This is because we joined the third table using a common field with the second, and this column is NULL for all rows from the first table not found in the second. As a result, these records are not matched to the third table, and they are subsequently dropped by the INNER JOIN in the last part of our query.

Time to Practice Multiple LEFT JOINs!

You have learned a lot about LEFT JOINs! You now even know the nuances of left-joining multiple tables in one SQL query. Let’s practice our newly acquired skills:

• Our SQL JOINs course provides comprehensive practice materials for different kinds of joins, including LEFT JOINs, INNER JOINs, self joins, non-equi joins, and of course, joins of multiple tables in one query.
• Our SQL Practice track provides multiple interactive hands-on SQL courses to practice different SQL constructions, like JOINs, GROUP BY, aggregation, and more.

Thanks for reading, and happy learning!