A left join combines data from two tables, keeping every row from the first table whether or not it has a match in the second
A left join is a way to combine information from two related tables in a database. When you use a left join, you get back every single row from the table on the left side of the join, plus any matching information from the table on the right side. If a row from the left table has no match in the right table, you still see that row — the right-side columns just show empty.
Think of it like keeping a master list and adding extra details to it. If some items on your master list don't have those extra details available, you keep the items anyway. The alternative joins (inner join, right join, full join) would either drop those items or handle the mismatch differently.
Key Takeaways
- A left join returns every row from the left table, even if there is no matching row in the right table.
- When there is no match, the columns from the right table show as empty or null in the result.
- Left joins are useful when you want to keep all records from your primary table and add optional information from a secondary table.
- The order matters — which table is "left" and which is "right" determines which rows you keep.
How a left join actually works
To understand a left join, picture two tables sitting side by side. The left table is your starting point — every row from it will appear in your result. The right table is the source of extra information. The join looks for rows where a specific column matches between the two tables (this is called the join condition).
When a match is found, the columns from both tables appear together in one row of the result. When no match exists, the row from the left table still appears, but the columns from the right table show as blank. This is the key difference from an inner join, which would discard that row entirely.
For example, if your left table lists all customers and your right table lists recent orders, a left join shows you every customer plus their orders if they have any. Customers with no orders still appear in the result — their order columns are just empty.
When you would actually use a left join
Left joins are the most common join type because they solve a real problem: you usually want to keep all records from your main table and add details when they exist. A business might have a table of all employees and a table of employees who took training last month. A left join shows every employee, with training dates filled in only for those who attended.
Another common case is inventory. You have a table of all products your store carries and a table of items currently in stock. A left join tells you which products are in stock and which are not — you see the full product list either way. If you used an inner join instead, you would only see products that are in stock, and you would lose track of what you are out of.
Left joins also help you find missing data. If you join a customer table to an orders table and see customers with empty order columns, you have found customers who have never ordered. That information is valuable for marketing or follow-up.
Left join versus other join types
An inner join returns only rows where both tables have a match. It is stricter — if a row from the left table has no match in the right table, that row disappears from the result. Inner joins are useful when you only want complete pairs of data.
A right join is the opposite of a left join. It keeps every row from the right table and adds matching information from the left table. Right joins are less common because you can usually reorder your tables and use a left join instead.
A full join keeps every row from both tables. If a row from the left table has no match in the right table, it appears with empty right-side columns. If a row from the right table has no match in the left table, it appears with empty left-side columns. Full joins are useful when you want to see everything, including unmatched rows from both sides.
| Join Type | What You Get | When to Use It |
|---|---|---|
| Left Join | All rows from the left table, plus matches from the right table | When you want to keep all records from your main table and add optional details |
| Inner Join | Only rows where both tables have a match | When you only want complete pairs of data |
| Right Join | All rows from the right table, plus matches from the left table | Rarely used; usually reorder tables and use a left join instead |
| Full Join | All rows from both tables, with empty columns where there is no match | When you want to see everything, including unmatched rows from both sides |
A concrete example you can picture
Imagine you run a small business with two tables. The first table lists all 50 customers. The second table lists customers who made a purchase in the last 30 days — only 35 of them. If you use a left join with the customer table on the left and the purchase table on the right, you get back all 50 customers. The 35 who purchased show their purchase date and amount. The 15 who did not purchase show empty columns for purchase date and amount.
If you had used an inner join instead, you would only see the 35 customers who purchased. You would have no record of the 15 inactive customers. If you had used a right join, you would still only see 35 rows — the right join would keep all rows from the purchase table, but there are only 35 purchases, so you would lose the inactive customers anyway.
Why the order of tables matters
The terms "left" and "right" refer to the order you write the tables in your query. The left table is the one you mention first, and the right table is the one you mention second. This order determines which rows you keep.
If you reverse the order and put the purchase table on the left and the customer table on the right, you would get a different result. Now you would only see the 35 customers who purchased, because the purchase table only has 35 rows. The 15 inactive customers would disappear. This is why choosing the correct table order matters — it changes what data you see.
Common mistakes people make with left joins
One frequent mistake is forgetting that a left join can still return no rows if the left table itself is empty. The join keeps all rows from the left table, but if there are zero rows to begin with, the result is empty.
Another mistake is using a left join when you actually need an inner join. If you only want records that have matches in both tables, an inner join is clearer and more efficient. Using a left join and then filtering out the empty rows wastes processing power.
People also sometimes confuse the join condition with a filter. The join condition (the part that says which columns must match) is different from a WHERE clause that filters the results afterward. A WHERE clause applied after a left join can accidentally remove the rows you wanted to keep.
Frequently Asked Questions
What does "null" mean in the empty columns after a left join?
Null is a database term for "no value" or "unknown." When a row from the left table has no match in the right table, the right-side columns show null instead of a number or text. Null is not the same as zero or an empty string — it means the data does not exist, not that it is zero.
Can I use a left join with more than two tables?
Yes. You can chain left joins together. The first join combines the first two tables, then the result joins with a third table, and so on. Each join keeps all rows from its left side and adds matching information from its right side.
Is a left join slower than an inner join?
Not necessarily. The speed depends on the database engine, the size of the tables, and how the join is written. A left join may be slightly slower because it has to track which rows have no match, but the difference is usually small. Write the join that answers your question correctly first, then optimize if speed becomes a problem.
What happens if the join condition matches multiple rows?
You get multiple result rows. If one customer has three orders, the left join creates three rows in the result — one for each order, with the customer information repeated. This is called a Cartesian product for that customer, and it is normal behavior.
Can I left join a table to itself?
Yes, and it is useful for certain problems. You might join an employee table to itself to match each employee with their manager, who is also an employee in the same table. This is called a self-join, and it works with left joins just like any other join.