Back to articles list January 13, 2021 - 7 minutes read What Is LEFT JOIN 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 learn sql sql joins 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. JOIN in SQL 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. customers idfirst_namelast_name 1SteveBrown 2HelenStewart 3JaneSmith 4JackBond orders iddatecustomer_idproduct_idamount 12020-12-20323213.99 22020-12-20312412.45 32020-12-2111889.95 42020-12-224854.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. customersorders idfirst_namelast_nameiddatecustomer_idproduct_idamount 1SteveBrown12020-12-2111889.95 2HelenStewart[NULL][NULL][NULL][NULL][NULL] 3JaneSmith32020-12-20323213.99 3JaneSmith32020-12-20312412.45 4JackBond42020-12-224854.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). employees idfirst_namelast_name 1JoeBiden 2BernieSanders 3PeterButtigieg 4ElizabethWarren 5MichaelBloomberg jan_bonuses idemployee_idamount 10113460.00 10222340.00 10341800.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; employeesjan_bonuses idfirst_namelast_nameidemployee_idamount 1JoeBiden10113460.00 2BernieSanders10222340.00 3PeterButtigieg[NULL][NULL][NULL] 4ElizabethWarren10341800.00 5MichaelBloomberg[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. countries idnamepopulationcurrencyarea 201United States330,770,244USD9,525,067 202China1,405,631,120CNY9,596,961 203Japan125,770,000JPY377,975 204Germany83,122,889EUR357,114 205India1,370,521,240INR3,287,263 gdp_2019 rankcountry_idgdp_nominal_MUSD 120121,427,700 220214,342,903 32035,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: SELECT id, name, population, gdp_nominal_MUSD FROM countries LEFT JOIN gdp_2019 ON countries.id = gdp_2019.country_id; countriesgdp_2019 idnamepopulationgdp_nominal_MUSD 201United States330,770,24421,427,700 202China1,405,631,12014,342,903 203Japan125,770,0005,081,770 204Germany83,122,889[NULL] 205India1,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! Tags: sql learn sql sql joins You may also like SQL JOIN Cheat Sheet This 2-page SQL JOIN Cheat Sheet covers the syntax of different JOINs (even the rare ones!) Download it in PDF or PNG format. Read more SQL JOIN Tips for Beginners Four tips for learning and using SQL JOINs. Our examples show you why these should become second nature when joining tables. Read more SQL JOIN Types Explained Learn how to join tables in SQL. Review the different SQL join types and when to use inner join, left join, right join, or full join. 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 How to Join the Same Table Twice Learn when and why you need to join the same table twice in SQL, including when you should use self joins. 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 What's the Difference Between Having Multiple Tables in FROM and Using JOIN? What’s your approach to SQL JOINs? Do you use JOIN or simply list tables in FROM? Learn why SQL practitioners prefer the new syntax with the JOIN keyword. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.