By Matthew Rathbone · November 16, 2026

A SQL LEFT JOIN returns every row from the left table (the one after FROM), plus the matching rows from the right table. When a left row has no match, you still get it, and the right table's columns come back as NULL. Use it whenever the question is "show me all X, and their Y if they have any": all customers and their orders, all students and their grades, all products and their sales.

SELECT columns
FROM left_table
LEFT JOIN right_table
  ON left_table.key = right_table.key;

LEFT JOIN and LEFT OUTER JOIN are the same thing. The word OUTER is optional in every major database.

Jump to: how it works · sample data · first example · LEFT vs INNER JOIN · find rows with no match · the WHERE clause trap · counting with LEFT JOIN · duplicates and multiple matches · multiple tables · only the first match · LEFT vs RIGHT vs FULL · self join · database differences · performance · mistakes · FAQ

What is a LEFT JOIN in SQL?

A join combines two tables into one result by matching rows on a condition, usually an ID. The type of join decides what happens to rows that don't match:

  • INNER JOIN drops them from both sides. You only see pairs that matched.
  • LEFT JOIN keeps unmatched rows from the left table and drops unmatched rows from the right table.

Here is what a LEFT JOIN does, step by step:

  1. Take each row from the left table.
  2. Find every row in the right table where the ON condition is true.
  3. If there are matches, output one combined row per match (one customer with two orders produces two rows).
  4. If there are no matches, output the left row once, with NULL in every right-table column.

So a LEFT JOIN never returns fewer rows than the left table has. It can return more, which surprises people. We'll come back to that in duplicates.

"Left" just means "the table written first". In FROM customers LEFT JOIN orders, customers is the left table. Swap the order and you change the meaning of the query.

Sample data

Every query below was run on SQLite 3.45, and the output is copied from the real result. LEFT JOIN syntax and behavior are the same in MySQL, PostgreSQL, SQL Server, Oracle and SQLite, so you can paste these into any of them.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, city TEXT);
INSERT INTO customers VALUES
 (1, 'Anna',  'Boston'),
 (2, 'Ben',   'Denver'),
 (3, 'Carla', 'Austin'),
 (4, 'Dev',   'Boston');

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER,
                     product TEXT, amount NUMERIC, status TEXT);
INSERT INTO orders VALUES
 (101, 1, 'Keyboard',  90, 'shipped'),
 (102, 1, 'Monitor',  210, 'shipped'),
 (103, 2, 'Mouse',     25, 'cancelled'),
 (104, 3, 'Monitor',  210, 'shipped'),
 (105, 9, 'Mouse',     25, 'shipped');

Four customers and five orders. Two details matter:

  • Dev has no orders. An inner join would make Dev disappear.
  • Order 105 belongs to customer 9, who doesn't exist. It has no match on the customers side.

SQL LEFT JOIN example

List every customer and what they ordered:

SELECT c.name, o.product, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
name  | product  | amount
------+----------+-------
Anna  | Keyboard | 90
Anna  | Monitor  | 210
Ben   | Mouse    | 25
Carla | Monitor  | 210
Dev   | NULL     | NULL
(5 rows)

Read the result row by row:

  • Anna has two orders, so she appears twice.
  • Ben and Carla have one order each.
  • Dev has no orders, but he's still there, with NULL for product and amount. That row is the whole point of a LEFT JOIN.
  • Order 105 is missing. Its customer doesn't exist, and a LEFT JOIN only protects rows from the left table.

The c and o after the table names are table aliases. They keep join queries short and tell the reader which table each column comes from.

Show something nicer than NULL

Wrap a right-table column in COALESCE to replace the NULL with a label or a zero:

SELECT c.name, COALESCE(o.product, '(no orders)') AS product
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.name;
name  | product
------+------------
Anna  | Keyboard
Anna  | Monitor
Ben   | Mouse
Carla | Monitor
Dev   | (no orders)
(5 rows)

LEFT JOIN vs INNER JOIN

Run the same query with INNER JOIN:

SELECT c.name, o.product, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;
name  | product  | amount
------+----------+-------
Anna  | Keyboard | 90
Anna  | Monitor  | 210
Ben   | Mouse    | 25
Carla | Monitor  | 210
(4 rows)

Same rows, minus Dev. That one row is the entire difference between the two joins.

INNER JOINLEFT JOIN
Matched rowsReturnedReturned
Left rows with no matchDroppedKept, right columns are NULL
Right rows with no matchDroppedDropped
Rows returnedOnly matched pairsAt least one per left row
Table order matters?NoYes
Typical question"Customers who have orders""All customers, with orders if any"

How to choose: if losing a row from the first table would make your answer wrong (a report that should list every customer, a count that should include zeros), use LEFT JOIN. If you only care about rows that have a match, use INNER JOIN. When in doubt, run both and compare row counts. If they differ, the difference is the unmatched rows.

LEFT JOIN vs LEFT OUTER JOIN

There is no difference. OUTER is an optional keyword in the SQL standard, so these two queries are identical and produce the same plan:

SELECT c.name, o.product FROM customers c LEFT JOIN       orders o ON o.customer_id = c.id;
SELECT c.name, o.product FROM customers c LEFT OUTER JOIN orders o ON o.customer_id = c.id;

Most people write LEFT JOIN because it's shorter. Pick one and use it consistently.

Find rows with no match (LEFT JOIN WHERE IS NULL)

One of the most useful LEFT JOIN patterns is finding what's missing: customers who never ordered, products that never sold, students who skipped an exam. Do the LEFT JOIN, then keep only the rows where the right side came back empty:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;
name
----
Dev
(1 rows)

Test a column that can never be NULL in a real match, such as the right table's primary key (o.id) or the join column. If you test a nullable column like o.status, you'll also catch real orders whose status happens to be empty.

This pattern is called an anti-join. The same question written with NOT EXISTS gives the same answer, and many people find it easier to read:

SELECT c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
name
----
Dev
(1 rows)

Both are fine, and modern databases usually run them the same way. Avoid NOT IN for this job: if the subquery returns a single NULL, NOT IN returns no rows at all. The SQL subquery guide shows that trap with real output.

LEFT JOIN with a WHERE clause: the most common bug

Say you want all customers, plus their shipped orders. The obvious query is wrong:

SELECT c.name, o.product, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'shipped';
name  | product  | status
------+----------+--------
Anna  | Keyboard | shipped
Anna  | Monitor  | shipped
Carla | Monitor  | shipped
(3 rows)

Ben and Dev are gone. Here's why. The database does the join first, so Dev's row has status = NULL and Ben's row has status = 'cancelled'. Then WHERE runs, and NULL = 'shipped' is not true, so the row is thrown away. A filter on the right table in WHERE quietly turns your LEFT JOIN into an INNER JOIN.

The fix: move the condition into the ON clause.

SELECT c.name, o.product, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
                  AND o.status = 'shipped';
name  | product  | status
------+----------+--------
Anna  | Keyboard | shipped
Anna  | Monitor  | shipped
Ben   | NULL     | NULL
Carla | Monitor  | shipped
Dev   | NULL     | NULL
(5 rows)

Now every customer is kept. The condition only decides which orders count as a match. Ben's cancelled order doesn't match, so Ben gets NULLs, exactly like Dev.

The rule of thumb:

  • Conditions on the right table go in ON (they decide what matches).
  • Conditions on the left table go in WHERE (they decide which left rows you want at all).

Filtering the left table in WHERE is safe and does what you'd expect:

SELECT c.name, o.product, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Boston';
name | product  | status
-----+----------+--------
Anna | Keyboard | shipped
Anna | Monitor  | shipped
Dev  | NULL     | NULL
(3 rows)

If you really do need a right-table filter in WHERE, add OR o.id IS NULL to keep the unmatched rows. Be aware that's a different question: it keeps customers with no orders but drops customers whose only orders fail the filter.

SELECT c.name, o.product
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.amount < 100 OR o.id IS NULL;
name | product
-----+---------
Anna | Keyboard
Ben  | Mouse
Dev  | NULL
(3 rows)

Carla vanished because her only order is $210. With the filter in ON instead, she'd stay with a NULL product. The same logic applies to any condition, for example AND o.amount > 100 in the ON clause:

SELECT c.name, o.product, o.amount
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
 AND o.amount > 100;
name  | product | amount
------+---------+-------
Anna  | Monitor | 210
Ben   | NULL    | NULL
Carla | Monitor | 210
Dev   | NULL    | NULL
(4 rows)

Counting with LEFT JOIN (and getting zeros)

LEFT JOIN plus GROUP BY is how you build reports that include zeros, like "orders per customer" where customers with no orders show 0. The catch is which COUNT you use:

SELECT c.name,
       COUNT(*)    AS count_star,
       COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY c.name;
name  | count_star | order_count
------+------------+------------
Anna  | 2          | 2
Ben   | 1          | 1
Carla | 1          | 1
Dev   | 1          | 0
(4 rows)

COUNT(*) counts rows, and Dev has one row (the one full of NULLs), so it says 1. COUNT(o.id) counts non-NULL values, so it correctly says 0. With a LEFT JOIN, always count a column from the right table. There's a longer walkthrough in how to include zero in a COUNT.

SUM, AVG, MIN and MAX return NULL for a group with no values, not 0. Wrap them in COALESCE if you want a zero:

SELECT c.name,
       SUM(o.amount)              AS total,
       COALESCE(SUM(o.amount), 0) AS total_or_zero
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY c.name;
name  | total | total_or_zero
------+-------+--------------
Anna  | 300   | 300
Ben   | 25    | 25
Carla | 210   | 210
Dev   | NULL  | 0
(4 rows)

LEFT JOIN duplicates and multiple matches

"Why does my LEFT JOIN return more rows than the left table?" is one of the most asked join questions. The answer is in step 3 of how it works: a LEFT JOIN outputs one row per match. Anna has two orders, so she appears twice. That's correct behavior, not a bug.

It becomes a bug when you join two different one-to-many tables at once. Add a payments table where Anna has made two payments:

CREATE TABLE payments (id INTEGER PRIMARY KEY, customer_id INTEGER, paid NUMERIC);
INSERT INTO payments VALUES (1, 1, 100), (2, 1, 200), (3, 3, 210);

Join orders and payments to customers in one query:

SELECT c.name, o.product, p.paid
FROM customers c
LEFT JOIN orders   o ON o.customer_id = c.id
LEFT JOIN payments p ON p.customer_id = c.id
ORDER BY c.name;
name  | product  | paid
------+----------+-----
Anna  | Keyboard | 100
Anna  | Keyboard | 200
Anna  | Monitor  | 100
Anna  | Monitor  | 200
Ben   | Mouse    | NULL
Carla | Monitor  | 210
Dev   | NULL     | NULL
(7 rows)

Anna's 2 orders × 2 payments = 4 rows. Orders and payments aren't related to each other, so the database pairs every order with every payment. Now sum them:

SELECT c.name,
       SUM(o.amount) AS ordered,
       SUM(p.paid)   AS paid
FROM customers c
LEFT JOIN orders   o ON o.customer_id = c.id
LEFT JOIN payments p ON p.customer_id = c.id
GROUP BY c.name
ORDER BY c.name;
name  | ordered | paid
------+---------+-----
Anna  | 600     | 600
Ben   | 25      | NULL
Carla | 210     | 210
Dev   | NULL    | NULL
(4 rows)

Anna really ordered $300 and paid $300. Both totals are doubled. This is called fan-out, and it's behind a lot of wrong numbers in dashboards.

The fix: aggregate each table first, then join the totals. Each subquery returns at most one row per customer, so nothing multiplies:

SELECT c.name,
       COALESCE(o.ordered, 0) AS ordered,
       COALESCE(p.paid, 0)    AS paid
FROM customers c
LEFT JOIN (SELECT customer_id, SUM(amount) AS ordered
           FROM orders GROUP BY customer_id) o ON o.customer_id = c.id
LEFT JOIN (SELECT customer_id, SUM(paid) AS paid
           FROM payments GROUP BY customer_id) p ON p.customer_id = c.id
ORDER BY c.name;
name  | ordered | paid
------+---------+-----
Anna  | 300     | 300
Ben   | 25      | 0
Carla | 210     | 210
Dev   | 0       | 0
(4 rows)

You can write the same thing with CTEs if you prefer naming each step. Don't "fix" fan-out with SELECT DISTINCT or SUM(DISTINCT ...). It hides the symptom and breaks as soon as two real rows share a value (see SQL DISTINCT).

Before trusting any join, check the row count. If it's bigger than you expect, look for a right table with several rows per key.

LEFT JOIN multiple tables

To LEFT JOIN three or more tables, chain the joins. Each new join attaches to the result so far. Add a products table:

CREATE TABLE products (name TEXT PRIMARY KEY, category TEXT);
INSERT INTO products VALUES
 ('Keyboard', 'Accessories'), ('Mouse', 'Accessories'), ('Monitor', 'Displays');
SELECT c.name, o.product, p.category
FROM customers c
LEFT JOIN orders   o ON o.customer_id = c.id
LEFT JOIN products p ON p.name = o.product
ORDER BY c.name;
name  | product  | category
------+----------+------------
Anna  | Keyboard | Accessories
Anna  | Monitor  | Displays
Ben   | Mouse    | Accessories
Carla | Monitor  | Displays
Dev   | NULL     | NULL
(5 rows)

Watch what happens if the second join is an INNER JOIN:

SELECT c.name, o.product, p.category
FROM customers c
LEFT JOIN orders   o ON o.customer_id = c.id
INNER JOIN products p ON p.name = o.product
ORDER BY c.name;
name  | product  | category
------+----------+------------
Anna  | Keyboard | Accessories
Anna  | Monitor  | Displays
Ben   | Mouse    | Accessories
Carla | Monitor  | Displays
(4 rows)

Dev is gone again. His row has o.product = NULL, which can't match any product, so the inner join removes it. Once you LEFT JOIN a table, every later join that depends on it should also be a LEFT JOIN, unless you mean to drop those rows. For more on chaining joins, see how to join 3 tables in SQL and joining multiple tables.

LEFT JOIN only the first match

Sometimes you want one row per left row: each customer with only their biggest (or latest) order. Number the right-table rows with ROW_NUMBER() in a subquery, then join only row 1. Put rn = 1 in the ON clause, not in WHERE, for the reason covered in the WHERE trap:

SELECT c.name, o.product, o.amount
FROM customers c
LEFT JOIN (
  SELECT customer_id, product, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id
                            ORDER BY amount DESC) AS rn
  FROM orders
) o ON o.customer_id = c.id AND o.rn = 1
ORDER BY c.name;
name  | product | amount
------+---------+-------
Anna  | Monitor | 210
Ben   | Mouse   | 25
Carla | Monitor | 210
Dev   | NULL    | NULL
(4 rows)

Change the ORDER BY inside OVER to pick a different "first", for example ORDER BY order_date DESC for the latest order. In SQL Server you can also use OUTER APPLY (SELECT TOP 1 ...), and in PostgreSQL LEFT JOIN LATERAL (... LIMIT 1) ON true.

LEFT JOIN vs RIGHT JOIN vs FULL OUTER JOIN

The three outer joins differ only in which side keeps its unmatched rows. To see the orphan order 105, put orders on the left:

SELECT o.id, o.product, c.name
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id;
id  | product  | name
----+----------+------
101 | Keyboard | Anna
102 | Monitor  | Anna
103 | Mouse    | Ben
104 | Monitor  | Carla
105 | Mouse    | NULL
(5 rows)

A RIGHT JOIN keeps every row from the second table. These two give the same rows (only the column order differs):

SELECT c.name, o.id AS order_id, o.product
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;
name  | order_id | product
------+----------+---------
Anna  | 101      | Keyboard
Anna  | 102      | Monitor
Ben   | 103      | Mouse
Carla | 104      | Monitor
NULL  | 105      | Mouse
(5 rows)

So A RIGHT JOIN B is just B LEFT JOIN A. Most teams stick to LEFT JOIN and reorder the tables, because queries read more naturally when the "keep everything" table comes first. More detail in the RIGHT JOIN guide.

A FULL OUTER JOIN keeps unmatched rows from both sides: Dev (no orders) and order 105 (no customer) both appear:

SELECT c.name, o.id AS order_id, o.product
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;
name  | order_id | product
------+----------+---------
Anna  | 101      | Keyboard
Anna  | 102      | Monitor
Ben   | 103      | Mouse
Carla | 104      | Monitor
Dev   | NULL     | NULL
NULL  | 105      | Mouse
(6 rows)
JoinKeeps unmatched rows fromRows here
INNER JOINNeither table4
LEFT JOINLeft table (customers)5 (adds Dev)
RIGHT JOINRight table (orders)5 (adds order 105)
FULL OUTER JOINBoth tables6 (adds both)

MySQL doesn't support FULL OUTER JOIN. Emulate it with a LEFT JOIN, plus the right-side orphans found with the anti-join pattern from earlier:

SELECT c.name, o.id AS order_id, o.product
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
UNION ALL
SELECT c.name, o.id, o.product
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

This returns the same six rows. See the FULL JOIN guide for more.

LEFT JOIN a table to itself

A self join joins a table to a second copy of itself. The classic example is employees and their managers, and it needs a LEFT JOIN, because the person at the top has no manager:

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, manager_id INTEGER);
INSERT INTO employees VALUES
 (1, 'Maya', NULL), (2, 'Omar', 1), (3, 'Lena', 1), (4, 'Theo', 2);

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
employee | manager
---------+--------
Maya     | NULL
Omar     | Maya
Lena     | Maya
Theo     | Omar
(4 rows)

With an inner join, Maya (the boss) would vanish from the list of employees.

LEFT JOIN in MySQL, PostgreSQL, SQL Server, Oracle and SQLite

The LEFT JOIN ... ON syntax is standard SQL and works the same way everywhere. The differences are in the extras:

DatabaseLEFT JOINRIGHT JOINFULL OUTER JOINNotes
MySQL / MariaDBYesYesNo (use UNION)Supports USING (col)
PostgreSQLYesYesYesLEFT JOIN LATERAL for per-row subqueries
SQL ServerYesYesYesOUTER APPLY; old *= syntax is removed
OracleYesYesYesLegacy (+) syntax still works but avoid it
SQLiteYesSince 3.39Since 3.39Older versions: swap tables, use LEFT JOIN

You'll sometimes see old Oracle code like WHERE o.customer_id(+) = c.id. That's Oracle's pre-1990s way of writing a LEFT JOIN. Oracle recommends the standard LEFT JOIN syntax instead (see the Oracle joins documentation). For the full list of join types in each engine, the PostgreSQL docs and MySQL JOIN reference are good references.

LEFT JOIN performance

A LEFT JOIN is not inherently slow. What makes joins slow is the database having to scan the whole right table for every left row. The fix is an index on the right table's join column:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Check with EXPLAIN (or EXPLAIN QUERY PLAN in SQLite). Without an index, SQLite even builds a temporary one on the fly, which is a strong hint you should create it:

EXPLAIN QUERY PLAN
SELECT c.name, o.product
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
SCAN c
BLOOM FILTER ON o (customer_id=?)
SEARCH o USING AUTOMATIC COVERING INDEX (customer_id=?) LEFT-JOIN

Two more tips:

  • A LEFT JOIN to a table you never select from, and whose key is unique, can be dropped by the optimizer in some databases, but not all. If you don't need a table, remove the join.
  • If you only need to know whether a match exists, EXISTS / NOT EXISTS stops at the first match and never multiplies rows.

Common LEFT JOIN mistakes

  1. Filtering the right table in WHERE. It turns the LEFT JOIN into an INNER JOIN. Put the condition in ON. (Example)
  2. Using COUNT(*) to count matches. Unmatched rows count as 1. Use COUNT(right_table.id). (Example)
  3. Joining two one-to-many tables and summing. Rows multiply and totals double. Aggregate each table first. (Example)
  4. Following a LEFT JOIN with an INNER JOIN on the same chain. The inner join drops the rows the LEFT JOIN kept. (Example)
  5. Putting the tables in the wrong order. The table you want to keep all rows from goes first, after FROM.
  6. Forgetting that right-table columns can be NULL. Calculations like o.amount * 2 return NULL for unmatched rows. Use COALESCE where you need a value.

FAQ

What does LEFT JOIN do in SQL?

It returns all rows from the left (first) table and the matching rows from the right table. Left rows without a match are still returned, with NULL in the right table's columns.

What is the difference between LEFT JOIN and INNER JOIN?

INNER JOIN returns only rows that match in both tables. LEFT JOIN also returns left-table rows that have no match. If every left row has a match, both give the same result.

Is LEFT JOIN the same as LEFT OUTER JOIN?

Yes. OUTER is optional, and the two are identical in MySQL, PostgreSQL, SQL Server, Oracle and SQLite.

What is the difference between LEFT JOIN and RIGHT JOIN?

LEFT JOIN keeps all rows from the first table, RIGHT JOIN keeps all rows from the second. A RIGHT JOIN B returns the same rows as B LEFT JOIN A.

Why does my LEFT JOIN return more rows than the left table?

Because a left row with several matches appears once per match. If you're joining two unrelated one-to-many tables, the matches multiply. Aggregate the right tables first, or pick one match with ROW_NUMBER().

How does LEFT JOIN work if there are multiple matches?

It returns one row for each match, repeating the left row's values. A customer with three orders appears three times.

How do I use 2 LEFT JOINs in one query?

Write them one after another: FROM a LEFT JOIN b ON ... LEFT JOIN c ON .... Each join attaches to everything before it. Watch out for row multiplication if b and c both have several rows per key.

Can I LEFT JOIN on multiple columns?

Yes. Combine conditions with AND: ON o.customer_id = c.id AND o.region = c.region. You can also add filters on the right table there, such as AND o.status = 'shipped'.

Why does LEFT JOIN return NULL values?

A NULL in a right-table column means that left row had no matching right row (or the matched row really contains NULL). Replace them with COALESCE(column, default).

Is LEFT JOIN slower than INNER JOIN?

Usually not by much. Both use the same join algorithms. INNER JOIN gives the optimizer more freedom to reorder tables, so it can be a little faster on complex queries, but you should choose the join that gives the correct answer and add an index on the join column.

Practice LEFT JOIN

The free interactive LEFT JOIN lesson runs in your browser with no signup. If joins are new to you, start with the JOIN lesson and multiple joins, then come back to LEFT JOIN and the self join lesson. After that, the SQL practice problems include join questions with solutions, and "find customers with no orders" is one of the most common SQL interview questions. Keep the SQL cheat sheet open for the syntax.

When you're ready to join tables in your own database, Beekeeper Studio is a free, open source SQL editor for PostgreSQL, MySQL, SQLite and SQL Server.