SQL guide · Joins

SQL LEFT JOIN: Return Every Row From the Left Table

A LEFT JOIN (also written LEFT OUTER JOIN) returns every row from the table on the left side of the join, together with the matching rows from the table on the right. When a left row has no match, the query still returns it, and every column that would have come from the right table is filled with NULL.

This makes it the right tool when the left table is your main subject and the right table is optional detail. Typical questions are "show all customers and their orders, including customers who never ordered" or "list every product with its latest review, if any". An inner join would hide the customers and products without a partner, while a left join keeps them visible.

Because unmatched rows come back with NULL, a left join is also the standard way to find rows that have no related record. Join the tables, then filter for NULL in a column from the right table. This pattern is called an anti-join and it answers questions such as which customers have never placed an order.

Basic syntax

SELECT t1.column_a, t2.column_b
FROM table1 AS t1
LEFT JOIN table2 AS t2
  ON t1.key_column = t2.key_column
[WHERE condition];

The table named before LEFT JOIN is the left table and is preserved in full. The table after it is the right table, and its columns are NULL when no row satisfies the ON condition. Conditions that restrict the right table belong in ON, while conditions on the final output belong in WHERE.

SQL LEFT JOIN examples

All customers, with orders when they exist

Every customer appears at least once. Customers with no orders show NULL in the order columns, while customers with several orders appear once per order.

SELECT c.customer_name, o.order_id, o.order_date
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
ORDER BY c.customer_name;

Find customers who never ordered

After the left join, rows without a match have NULL in the right table key. Filtering on that column keeps only the customers with no orders, which is the anti-join pattern.

SELECT c.customer_id, c.customer_name
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

Counting related rows, including zero

Counting a column from the right table counts only matched rows, so customers without orders correctly get a count of 0. Using COUNT(*) here would wrongly report 1 for those customers.

SELECT c.customer_name, COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
GROUP BY c.customer_name
ORDER BY order_count DESC;

Filtering the right table inside ON

To keep all customers but only join orders from 2026, put the date test in the ON clause. Moving it to WHERE would remove customers with no 2026 orders and turn the query into an inner join.

SELECT c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.order_date >= '2026-01-01';

Where SQL dialects differ

PostgreSQL: LEFT JOIN and LEFT OUTER JOIN are identical. USING (col) is supported and merges the join column into one output column. LEFT JOIN LATERAL is available for per-row subqueries.

MySQL: Supports LEFT JOIN with ON or USING. Because MySQL does not have a FULL OUTER JOIN, a left join is one half of the usual workaround, combined with a right join through UNION.

SQL Server: LEFT JOIN and LEFT OUTER JOIN are equivalent. The old *= operator is no longer supported in current compatibility levels, so use the ANSI syntax. OUTER APPLY offers a left-join-like form for table-valued functions.

SQLite: Supports LEFT JOIN and LEFT OUTER JOIN. SQLite did not support RIGHT JOIN before version 3.39.0 (2022), so older code often rewrites right joins as left joins by swapping tables.

Oracle: Supports ANSI LEFT OUTER JOIN. Legacy queries use the (+) marker on the right-table columns in the WHERE clause, which is Oracle specific and cannot express every outer join.

Common mistakes

  • Filtering the right table in WHERE, for example WHERE o.status = 'paid'. Unmatched rows have NULL status, so they are discarded and the left join behaves like an inner join. Put the condition in ON instead.
  • Using COUNT(*) after a left join. It counts the preserved row even when there is no match, so use COUNT(right_table.column) to count only real matches.
  • Swapping the table order without noticing. A LEFT JOIN B and B LEFT JOIN A return different sets of preserved rows.
  • Assuming the result has one row per left row. If the right side has several matches, the left row is repeated, which inflates sums taken from left-side columns.
  • Testing a right-side column for NULL when that column can legitimately be NULL. Test the join key or primary key of the right table to detect a missing match.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator