How a Left Join Works in Database Queries

A left join combines rows from two tables, keeping all rows from the left table even if there's no match in the right table

When you write a database query that joins two tables, you're asking the database to match rows based on a condition — usually a shared ID or name. A left join is one way to do this matching. It keeps every row from the first (left) table you named, and adds columns from the second (right) table only where a match exists. If there's no match, those columns show empty or null.

This matters because the other common join types — inner join, right join, full outer join — all drop rows under different conditions. A left join is the safest choice when you want to keep all your original data and just add extra information where it's available.

Key Takeaways

A left join returns every row from the left table, with matching data from the right table added where it exists.
If a row in the left table has no match in the right table, the columns from the right table appear empty or null in the result.
The order matters: LEFT JOIN table_b is different from RIGHT JOIN table_a, because left and right refer to the table names in the order you write them.
Left joins are useful when you want to keep all your original records and just add extra details from another table without losing any rows.

How the matching works

The join condition — usually written as ON — tells the database which columns to use for matching. For example, if you have a customers table and an orders table, you might join them ON customers.id = orders.customer_id. This means: for each customer, find all orders where the customer_id matches that customer's id.

In a left join, the database starts with the left table (customers in this example) and goes through every row. For each customer, it looks in the right table (orders) for matching rows. If it finds matches, it adds them. If it finds no matches, it still keeps that customer row, but the order columns show as empty.

This is why left joins are common in real work: you often want to see all your customers even if some have never placed an order, or all your employees even if some haven't been assigned to a project yet.

Left join versus inner join

An inner join returns only rows where a match exists in both tables. If you inner join customers and orders, you'll see only customers who have placed at least one order. Any customer with no orders disappears from the result.

A left join keeps those customers. You'll see every customer, and for those with orders, the order details appear. For those without orders, the order columns are blank. The difference is whether you want to lose rows that don't match.

In practice: use an inner join when you only care about records that exist in both tables. Use a left join when you want to keep all records from the left table and just add information where it's available.

What happens when there are multiple matches

If one customer has placed five orders, a left join will return five rows for that customer — one for each order, with the customer details repeated. This is correct behavior, but it's easy to miss: you might think you're getting one row per customer and accidentally count the same customer five times.

If you want one row per customer with a count of orders, you need to add GROUP BY and COUNT() to your query. The left join itself just matches and combines rows; it doesn't collapse them.

The order of tables matters

LEFT JOIN table_b keeps all rows from the table you started with. If you write SELECT * FROM customers LEFT JOIN orders, you keep all customers. If you write SELECT * FROM orders LEFT JOIN customers, you keep all orders instead.

This is why the terms "left" and "right" exist: they refer to the position in the query. The left table is the one you named first (after FROM). The right table is the one you named after JOIN. Switching them changes which rows you keep.

Null values in the result

When a row from the left table has no match in the right table, the columns from the right table show as NULL — a database term for empty or missing. In most database tools, NULL appears as blank space or the word NULL.

This matters when you write conditions later in your query. If you add WHERE orders.id IS NOT NULL, you're filtering to keep only rows where a match was found — which turns your left join into an inner join. If you want to keep the non-matching rows, avoid WHERE conditions on the right table's columns.

Common mistakes and how to avoid them

The most common mistake is forgetting that a left join can return multiple rows per left-table record. If you're counting customers and you count rows instead of distinct customer IDs, you'll get the wrong answer. Use COUNT(DISTINCT customers.id) to count unique customers.

Another mistake is putting a condition on the right table in the WHERE clause instead of the ON clause. WHERE orders.amount > 100 filters after the join, which removes non-matching rows and defeats the purpose of a left join. If you want to filter the right table before joining, put the condition in the ON clause: ON customers.id = orders.customer_id AND orders.amount > 100.

A third mistake is assuming NULL means zero. If a customer has no orders, the order amount is NULL, not 0. If you sum order amounts, NULL is ignored (which is usually what you want), but if you're doing math, be aware that NULL plus anything equals NULL.

When to use a left join instead of other options

Use a left join when you want to keep all rows from your main table and just add extra information. This is the most common join in real databases because most queries start with a primary table (customers, employees, products) and add details from related tables.

Use an inner join if you only care about records that exist in both tables. Use a right join if you want to keep all rows from the right table instead (though this is rare — you can usually rewrite it as a left join by switching the table order). Use a full outer join if you want to keep all rows from both tables, matching where possible.

Frequently Asked Questions

Can I left join more than two tables?

Yes. You can chain left joins: FROM table_a LEFT JOIN table_b ON ... LEFT JOIN table_c ON ... Each join keeps all rows from the left side of that join. The result keeps all rows from table_a, adds matches from table_b, then adds matches from table_c.

What's the difference between LEFT JOIN and LEFT OUTER JOIN?

They're the same thing. OUTER is optional — most databases treat LEFT JOIN and LEFT OUTER JOIN identically. Some people write OUTER for clarity, but it's not required.

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

Because the right table has multiple matches for some rows in the left table. If one customer has five orders, the join returns five rows for that customer. This is correct. If you want one row per customer, use GROUP BY.

How do I keep only rows where there's no match?

Use a left join and then filter for NULL in the right table: WHERE orders.id IS NULL. This returns all customers who have no orders. This pattern is called an anti-join.

Does the order of columns in the ON clause matter?

No. ON customers.id = orders.customer_id returns the same result as ON orders.customer_id = customers.id. The database treats them as equivalent.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.