A left join returns all rows from your left table, plus matching rows from your right table

A left join is a way to combine data from two tables in SQL. It keeps every row from the table on the left side of the join, and adds columns from the table on the right side whenever there's a match. If there's no match, the columns from the right table show as empty (NULL).

The key difference from other joins is that a left join never drops rows from your left table. Even if a row has no matching partner in the right table, it stays in your results. This makes left joins useful when you want to see everything from one table and fill in extra information where it exists.

Key Takeaways

  • A left join keeps all rows from the left table and adds matching data from the right table, leaving blanks where no match exists.
  • The syntax is SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id, where table1 is the left table and table2 is the right table.
  • Left joins are commonly used to find missing data, like customers who have never placed an order or employees with no assigned projects.
  • The ON clause determines which rows count as a match between the two tables, and you can join on any column, not just primary keys.

The basic syntax and how to read it

The structure of a left join looks like this:

SELECT * FROM customers LEFT JOIN orders ON customers.id = orders.customer_id

Break this down piece by piece. SELECT * means grab all columns. FROM customers names your left table — this is the table whose rows you want to keep. LEFT JOIN orders adds a second table to the query. ON customers.id = orders.customer_id is the matching rule: connect rows where the customer ID in the customers table matches the customer ID in the orders table.

The result includes every customer, even those with no orders. For customers who have orders, you see their order details in the added columns. For customers with no orders, those columns are blank.

Left join versus inner join versus right join

An inner join returns only rows that have a match in both tables. If a customer has no orders, they don't appear at all. An inner join is stricter — it filters your results to only the pairs that connect.

A right join does the opposite of a left join: it keeps all rows from the right table and adds matching rows from the left. A full outer join keeps all rows from both tables, filling in blanks on both sides when matches don't exist. Most databases support left and inner joins; full outer joins are less common and not available in all SQL systems.

Choose a left join when you want to see everything from one specific table and optionally add information from another. Choose an inner join when you only care about rows that connect in both tables. The choice depends on what question you're trying to answer with your data.

Real examples: customers and orders

Imagine a customers table with three rows: Alice (ID 1), Bob (ID 2), and Carol (ID 3). Your orders table has two rows: an order from Alice and an order from Bob. No one has ordered from Carol.

A left join on these tables returns three rows. Alice's row includes her order details. Bob's row includes his order details. Carol's row appears with blank spaces where order information would go. An inner join would return only two rows — Alice and Bob — because Carol has no matching order.

This matters in real work. If you're a manager checking which customers have never ordered, a left join shows you Carol immediately. An inner join would hide her from the results entirely, and you'd have to run a separate query to find inactive customers.

When to use a left join in your own queries

Use a left join when you're looking for missing data or gaps. Common scenarios include finding customers with no orders, employees with no assigned projects, products that have never been purchased, or users who registered but never logged in.

Left joins also work well when you want a complete picture of one table with optional details from another. For example, you might join a list of all employees with a table of performance reviews, knowing that some employees may not have a review yet. The left join shows you everyone, with review scores filled in where they exist.

Another use is building reports. If you're creating a monthly summary of all customers and their total spending, a left join ensures every customer appears in the report, even those who spent nothing that month. Without the left join, you'd accidentally exclude your inactive customers from the report.

Common mistakes and how to avoid them

The most common mistake is confusing which table is "left" and which is "right". The left table is the one in your FROM clause. The right table is the one after LEFT JOIN. If you reverse them, you get different results. Always double-check your table order if your results look wrong.

Another mistake is writing the wrong matching condition in the ON clause. If you join on the wrong columns, you'll get nonsensical matches or far too many rows. Make sure the columns you're joining on actually represent the same thing — usually an ID that connects the two tables.

A third mistake is forgetting that NULL values (blanks) appear in the results. If you later filter your results with a WHERE clause, remember that WHERE filters out rows with NULL values. If you want to keep those rows, use the ON clause instead to set up your join, not WHERE to filter afterward.

Left join with multiple tables

You can chain left joins together to connect more than two tables. For example:

SELECT * FROM customers LEFT JOIN orders ON customers.id = orders.customer_id LEFT JOIN products ON orders.product_id = products.id

This query starts with all customers, adds their orders, and then adds product details for each order. Each join keeps all rows from the previous result, so you still see every customer even if they have no orders.

When chaining joins, the order matters. Each join operates on the result of the previous join, so think through your logic carefully. If you need all customers, then all their orders, then all product details for those orders, the sequence above works. If you need something different, you may need to rearrange the joins or use a different join type for one of the steps.

Frequently Asked Questions

What does NULL mean in a left join result?

NULL is a blank value that appears when there's no match in the right table. If a customer has no orders, the order columns show NULL. NULL is not the same as zero or an empty string — it means the data doesn't exist. Many SQL functions treat NULL specially, so be aware of it when writing WHERE clauses or calculations.

Can I left join on columns that aren't ID numbers?

Yes. The ON clause can use any columns from both tables. You might join on email addresses, names, dates, or any other column. The only requirement is that the join makes logical sense — you're matching rows where the values in those columns are the same.

Why does my left join return more rows than my left table?

This happens when a single row in the left table matches multiple rows in the right table. If a customer placed three orders, that customer appears three times in the result — once for each order. This is correct behavior, but it surprises people who expect one row per customer. Use GROUP BY or COUNT if you want to collapse multiple matches into a summary.

Is a left join slower than an inner join?

Not necessarily. Both types of joins perform similarly in most databases. The speed depends on the size of your tables, the columns you're joining on, and whether those columns have indexes. Write the join that answers your question correctly, then optimize if performance becomes a problem.

Can I use a left join with more than two tables?

Yes. You can chain multiple left joins together, and each one keeps all rows from the previous result. Just remember that each join adds more columns and potentially more rows if there are multiple matches in the right table.