How LEFT JOIN Works in SQL and When to Use It

A LEFT JOIN returns all rows from the left table, plus matching rows from the right table

A LEFT JOIN is a way to combine data from two tables in SQL. It keeps every row from the first table (called the "left" table) and adds columns from the second table (called the "right" table) only when there is a match. If there is no match, the columns from the right table show as empty or null.

The key difference from other joins is that LEFT JOIN never drops rows from the left table. If you join a customers table to an orders table with LEFT JOIN, you will see every customer, even those who have never placed an order. With an INNER JOIN, you would only see customers who have orders.

Key Takeaways

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.
Use LEFT JOIN when you need to see all records from one table even if some have no match in the other.
NULL values in the result show where the right table had no matching row for a left table row.

The basic syntax and how to read it

The structure of a LEFT JOIN looks like this:

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id

Breaking this down: FROM table1 is your left table. LEFT JOIN table2 adds the right table. ON table1.id = table2.id tells SQL which columns to match between the two tables. The ON clause is the condition that decides whether a row from table2 belongs with a row from table1.

You can also specify which columns to return instead of using *. For example, SELECT table1.name, table2.order_date FROM table1 LEFT JOIN table2 ON table1.id = table2.id would return only the name from table1 and the order date from table2.

LEFT JOIN versus INNER JOIN and other join types

An INNER JOIN returns only rows where both tables have a match. If a customer has no orders, an INNER JOIN will not show that customer at all. A LEFT JOIN shows that customer with NULL values in the order columns.

A RIGHT JOIN does the opposite: it keeps all rows from the right table and adds matches from the left. A FULL OUTER JOIN (supported in some databases like PostgreSQL and SQL Server, but not MySQL) keeps all rows from both tables, filling in NULL where either side has no match.

The choice depends on what you need to see. If you want to find customers with no orders, LEFT JOIN is the right choice. If you only care about customers who have actually ordered, INNER JOIN is faster and cleaner.

A real example: customers and orders

Imagine a customers table with columns id, name, and email, and an orders table with columns id, customer_id, and amount. You want to see each customer and how much they have spent, including customers who have not ordered yet.

The query would be:

SELECT customers.name, orders.amount FROM customers LEFT JOIN orders ON customers.id = orders.customer_id

The result will have one row for each order, plus one row for each customer with no orders (where the amount column is NULL). If Sarah has placed three orders and Mike has placed none, you will see three rows for Sarah and one row for Mike with NULL in the amount column.

If you want to count orders per customer or sum the amounts, you would add a GROUP BY clause: SELECT customers.name, COUNT(orders.id) FROM customers LEFT JOIN orders ON customers.id = orders.customer_id GROUP BY customers.id. This gives you one row per customer with a count of their orders, showing 0 for customers with no orders.

When NULL values appear and what they mean

NULL in a LEFT JOIN result always means "no matching row was found in the right table." It is not the same as an empty string or zero. NULL is the absence of a value.

You can filter for these unmatched rows using WHERE table2.id IS NULL. This finds all rows from the left table that have no match in the right table. For the customer example, WHERE orders.id IS NULL would show only customers with no orders.

Be careful with NULL in calculations. If you sum an amount column that contains NULL, SQL will ignore the NULL values, not treat them as zero. This is usually what you want, but it is worth knowing.

Performance and when to use LEFT JOIN

LEFT JOIN is slower than INNER JOIN because the database has to check every row in the left table and look for matches, then include rows with no match. If you have a large left table and only a small number of rows will actually match, the performance difference can be noticeable.

Use LEFT JOIN when you specifically need to see all rows from the left table, even unmatched ones. Common cases include finding customers with no orders, employees with no projects, or products that have never been sold. If you only care about matched rows, use INNER JOIN instead.

Indexes on the join columns (the columns in the ON clause) make LEFT JOIN faster. If you are joining on customer_id, make sure that column is indexed in both tables.

Multiple LEFT JOINs in one query

You can chain multiple LEFT JOINs together. For example:

SELECT customers.name, orders.amount, products.name FROM customers LEFT JOIN orders ON customers.id = orders.customer_id LEFT JOIN products ON orders.product_id = products.id

This keeps all customers, adds their orders, and then adds the product names for each order. If a customer has no orders, both the orders and products columns will be NULL. If a customer has an order but the product was deleted, the product name will be NULL while the order amount is not.

The order matters. Each LEFT JOIN is applied to the result of the previous one. The first LEFT JOIN adds the orders table to customers. The second LEFT JOIN adds the products table to that result.

Frequently Asked Questions

What is the difference between LEFT JOIN and LEFT OUTER JOIN?

They are the same thing. LEFT OUTER JOIN is the full name, and LEFT JOIN is the shorthand. Most databases accept both, and most developers use LEFT JOIN because it is shorter.

Can I use LEFT JOIN with more than two tables?

Yes. You can chain multiple LEFT JOINs in a single query. Each one adds another table to the result, keeping all rows from the previous result and adding matches from the new table.

Why am I getting duplicate rows when I use LEFT JOIN?

This usually happens when the right table has multiple rows that match a single row in the left table. If a customer has three orders, a LEFT JOIN will show that customer three times, once per order. Use GROUP BY and aggregate functions like COUNT or SUM if you want one row per customer.

How do I find rows that did not match in a LEFT JOIN?

Add WHERE right_table.id IS NULL to your query. This filters for rows where the right table had no match, showing only the unmatched rows from the left table.

Is LEFT JOIN slower than INNER JOIN?

Yes, typically. LEFT JOIN has to check every row in the left table and include unmatched ones, while INNER JOIN can stop as soon as it finds a match. The difference is usually small unless your tables are very large, but use INNER JOIN if you only need matched rows.

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.