# SQL LEFT JOIN: Return Every Row From the Left Table

- URL: https://betteranalyst.com/sql/left-join

SQL guide · Joins

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

- [SQL INNER JOIN](https://betteranalyst.com/sql/inner-join)
- [SQL RIGHT JOIN](https://betteranalyst.com/sql/right-join)
- [SQL FULL OUTER JOIN](https://betteranalyst.com/sql/full-outer-join)
- [SQL IS NULL](https://betteranalyst.com/sql/is-null)
- [SQL GROUP BY](https://betteranalyst.com/sql/group-by)
- [SQL WHERE](https://betteranalyst.com/sql/where)

[Browse all SQL guides](https://betteranalyst.com/sql)

## 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](https://betteranalyst.com/ai-sql-query-generator)