SQL LEFT JOIN: return rows with optional matches

SQL LEFT JOIN returns every row from the left table and attaches matching rows from the right table. In SQL terms, it’s a SQL outer join: when no match exists, the right-side columns are filled with NULL. Use it when you need all records from one table regardless of whether related data exists — such as listing all customers whether or not they placed orders. LEFT JOIN and LEFT OUTER JOIN are identical keywords; the OUTER is optional.

SQL LEFT JOIN Example For Returning All Rows

SQL Editor

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);
INSERT INTO customers VALUES (1,'Alma'),(2,'Ben'),(3,'Cara');
INSERT INTO orders VALUES (1,1,79.00),(2,1,150.00),(3,3,45.00);

-- All customers with their order totals (NULLs where no order exists)
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.id;

Output:

Output will appear here...

Output:

+------+-------+
| name | total |
+------+-------+
| Alma | 79    |
| Alma | 150   |
| Ben  | NULL  |
| Cara | 45    |
+------+-------+
4 row(s)

How This Example Works

customers holds three people; orders holds three orders linked by customer_id.
LEFT JOIN orders o ON o.customer_id = c.id pairs each customer with every order that matches their id. Alma has two orders, so she produces two output rows.
Ben has no orders. LEFT JOIN keeps his row and fills o.total with NULL instead of dropping him from the result (which is what INNER JOIN would do).
ORDER BY c.id, o.id keeps the output grouped by customer and stable within each customer. The NULL in Ben’s total column signals the absence of a match rather than a zero-value order.

ON vs WHERE: Why Filter Placement Matters

Placing a right-table filter in WHERE instead of ON is the most common LEFT JOIN mistake — it silently converts the outer join into an inner join.

-- Wrong: WHERE drops Ben because his o.total is NULL
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.total > 50;

-- Right: ON filters during join matching, Ben stays with NULLs
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.total > 50;

ON conditions are evaluated during the join: unmatched left rows still appear with NULLs. WHERE runs after the join, so it filters out any row where the right column is NULL — eliminating the exact rows LEFT JOIN was meant to preserve.

Anti-Join: Finding Rows With No Match

LEFT JOIN combined with an IS NULL check on a right-table column is a standard way to find missing related records:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

This returns only Ben — the customer with zero orders. The pattern works because o.id is NULL only for unmatched left rows. NOT EXISTS achieves the same result and can be clearer when no right-table columns are needed in the output.

LEFT JOIN vs INNER JOIN

LEFT JOIN	INNER JOIN
All left rows, NULLs for unmatched right columns	Only rows that match on both sides
Row count ≥ left-table row count	Row count ≤ smaller table’s row count
Use when unmatched rows carry meaning (reports, completeness checks)	Use when every result row must have valid data from both tables

Rule: start with LEFT JOIN when preserving all left rows matters. Switch to INNER JOIN when unmatched rows are irrelevant or invalid.

Common Mistakes With SQL LEFT JOIN

Row multiplication in one-to-many joins

LEFT JOIN produces one output row per matching pair. If one customer has ten orders, that customer appears ten times. Aggregating without accounting for this inflates totals.

-- Wrong: counts order rows, not customers
SELECT COUNT(*) FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

-- Right: count distinct customers
SELECT COUNT(DISTINCT c.id) FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

Using SELECT * with joins

SELECT * in a join query returns duplicate column names (both c.id and o.id), which causes confusion in application code. Always list the columns you need.

Performance Notes

Index the join key on the right table (orders.customer_id) so the engine can probe for matches instead of scanning. For large right tables where only a subset of rows is relevant, pre-filter with a subquery or move the filter into the ON clause — not WHERE — to reduce the join work while keeping LEFT JOIN semantics intact.

FAQ

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

None. LEFT JOIN and LEFT OUTER JOIN are synonyms defined by the SQL standard. Every major database (PostgreSQL, MySQL, SQLite, SQL Server, Oracle) treats them identically. Most developers omit OUTER for brevity.

Why does my LEFT JOIN return fewer rows after adding a WHERE clause?

A WHERE filter on a right-table column eliminates NULL-extended rows, turning the LEFT JOIN into an effective INNER JOIN. Move the condition into the ON clause to keep unmatched left rows in the result.

When should I use LEFT JOIN instead of INNER JOIN?

Use LEFT JOIN when the left-side rows must appear in output regardless of matches — completeness reports, missing-data checks, and optional enrichment. Use INNER JOIN when only matched rows are valid and unmatched rows should be excluded.