Back to articles list March 10, 2020 - 8 minutes read How to LEFT JOIN Multiple Tables in SQL Kateryna Koidan Kateryna is a data science writer from Kyiv, Ukraine. She worked for BNP Paribas, the leading European banking group, as an internal auditor for more than 6 years. More recently, she decided to pursue only the favorite part of her job—data analysis. Now she is continuing her self-education with deep-learning courses, enjoys coding for data analysis and visualization projects, and writes on the topics of data science and artificial intelligence. Kateryna is also a proud mother of two lovely toddlers, who make her life full of fun. Tags: sql left join joins 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. 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 idfirst_namelast_namegenderagecustomer_since 1DanielBlackM342014-10-13 2ErikBrownM252015-06-10 3DianaTrumpF392015-10-25 4AnnaYaoF192017-02-20 5ChristianSandersM422018-01-31 Sales iddatebook_idcustomer_idquantityamount 12019-09-0223114.99 22019-10-0112112.99 32019-10-0134115.75 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. idfirst_namelast_namegenderagecustomer_sincesales_datetotal_spent 1DanielBlackM342014-10-13[NULL][NULL] 2ErikBrownM252015-06-102019-10-0112.99 3DianaTrumpF392015-10-252019-09-0214.99 4AnnaYaoF192017-02-202019-10-0115.75 5ChristianSandersM422018-01-31[NULL][NULL] 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. 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 idcampaigncustomer_iddate 1SMS_discount1022019-09-01 2SMS_discount1032019-09-01 3SMS_discount1052019-09-01 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: idfirst_namelast_namegenderagecustomer_sincesalepromotion 1DanielBlackM342014-10-13[NULL][NULL] 2ErikBrownM252015-06-102019-10-012019-09-01 3DianaTrumpF392015-10-252019-09-022019-09-01 4AnnaYaoF192017-02-202019-10-01[NULL] 5ChristianSandersM422018-01-31[NULL]2019-09-01 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 idnameauthorgenrequantityprice 1The Lord of the RingsJ. R. R. Tolkienfantasy712.99 2LolitaVladimir Nabokovnovel414.99 4The HobbitJ. R. R. Tolkienfantasy1010.75 5Death on the NileAgatha Christiedetective89.75 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). idfirst_namelast_namesalebookgenre 1DanielBlack[NULL][NULL][NULL] 2ErikBrown2019-10-01The Lord of the Ringsfantasy 3DianaTrump2019-09-02Lolitanovel 4AnnaYao2019-10-01[NULL][NULL] 5ChristianSanders[NULL][NULL][NULL] 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; idfirst_namelast_namesalebookgenre 2ErikBrown2019-10-01The Lord of the Ringsfantasy 3DianaTrump2019-09-02Lolitanovel 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. Get additional practice with LEFT JOINs and other non-INNER JOINs in our SQL Basics course. Thanks for reading, and happy learning! Tags: sql left join joins You may also like An Illustrated Guide to the SQL OUTER JOIN An SQL JOIN clause links the data from two or more database tables. Let’s find out what is the OUTER JOIN and how does it work! Read more How to Learn SQL JOINs Learn how to use SQL JOINs to effectively combine data across multiple tables and analyze sophisticated data sets. Read more Subquery vs. JOIN What are the differences between a subquery and a JOIN in SQL? How do you choose one over the other? See examples of when to use which. Read more How to Join 3 Tables (or More) in SQL Have you ever wondered how to join three tables in SQL? It's easy when you know the basics. Joining three tables can be as easy as joining two tables. Read more How to Practice SQL JOINs Do you want to master SQL JOINs? The best way is through practice. Here’s how to practice SQL JOINs along with some examples. Read more How to LEFT JOIN Multiple Tables in SQL You will learn how to left join 3 tables in SQL while avoiding common mistakes in joining multiple tables. Examples included! Read more SQL INNER JOIN Explained in Simple Words Learn how to join tables in SQL using the most famous among SQL JOIN types–the INNER JOIN. Read more Converting Subqueries to Joins No more badly performed queries! Find out how you can convert SQL subqueries to joins and improve your query efficiency. Read more An Illustrated Guide to Multiple Join Did you wonder what is the most efficient way of making multiple joins in SQL? We did as well - come by and check out our illustrated guide! Read more An Illustrated Guide to the SQL Non Equi Join SQL join doesn’t have to be based on identical matches! In this post, we look at the non-equi join, which uses ‘non-equal’ operators to match records. Read more An Illustrated Guide to the SQL Self Join What is a SQL self join and how does it work? When should you use a self join in SQL? In this article, you’ll find answers to these questions! Read more An Illustrated Guide to the SQL INNER JOIN INNER JOIN combines data from multiple tables by joining them based on a matching record. It requires a JOIN condition, which we'll explain in this article. Read more An Illustrated Guide to the SQL CROSS JOIN CROSS JOIN returns a Cartesian product, or all records joined to all records in all tables. Take a look at the example queries. Read more An Introduction to Using SQL Aggregate Functions with JOINs Aggregate functions. Powerful SQL tools. Let's see how they cooperate paired with LEFT JOIN, SUM and GROUP BY perform computations on multiple tables. Read more Learning JOINs With Real World SQL Examples JOIN statement lets you work with data stored in multiple tables. This article is a practical introduction to the SQL JOIN. Check out examples in real life. Read more SQL JOINs A SQL JOIN is a method to retrieve data from two or more database tables. Here we present a basic overview of what data from a particular SQL join will look Read more SQL JOINs for Beginners JOIN data from two or more tables with no effort. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.