SQL LEFT JOIN: Syntax, Examples & Practice

The Playground database has a users table of customers and an orders table. Not every customer has ordered. To list customers with their orders without losing the ones who never bought anything, join the two tables with LEFT JOIN:

SELECT u.id, u.first_name, o.id AS order_id, o.total
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
WHERE u.id <= 5
ORDER BY u.id, o.id;

▶ Run this query in the SQL Playground

Read the result row by row. Jennifer and Donna have one order each, so each gets one row. Kenneth has no orders, but he is still there: the two columns that come from orders are NULL. Michael has three orders, so he appears three times. Five customers went in and 7 rows came out.

That is the whole idea. users is written first, so it is the left table and all of its rows are kept. orders is the right table: its rows are attached where the ON condition is true, and replaced by NULLs where it is not. An INNER JOIN would have dropped Kenneth.

For each row of the left table, the database looks for rows in the right table that satisfy ON:

So a LEFT JOIN can never return fewer rows than the left table, but it can return more. Counting the full join of customers and orders shows all three cases at once:

SELECT COUNT(*)             AS result_rows,
       COUNT(DISTINCT u.id) AS customers,
       COUNT(o.id)          AS matched_orders,
       COUNT(*) - COUNT(o.id) AS rows_without_a_match
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id;

▶ Run this query in the SQL Playground

All 150 customers are present. The 200 orders each produce a row, and the 36 customers who never ordered add one NULL row each: 200 + 36 = 236 rows.

The only difference is what happens to left rows that have no match. INNER JOIN drops them; LEFT JOIN keeps them.

SELECT 'LEFT JOIN' AS join_type, COUNT(*) AS rows_returned
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
UNION ALL
SELECT 'INNER JOIN', COUNT(*)
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

▶ Run this query in the SQL Playground

The 36-row gap is exactly the customers without orders. A "number of customers" report built on the INNER JOIN would quietly leave them out.

The SQL JOINs overview compares all the join types side by side.

NULL in a LEFT JOIN result means "no matching row". It is not zero and not an empty string, which matters as soon as you aggregate. This report shows orders and spending per customer, including customers with none:

SELECT u.id, u.first_name,
       COUNT(o.id) AS orders,
       COALESCE(ROUND(SUM(o.total), 2), 0) AS total_spent
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
WHERE u.id <= 6
GROUP BY u.id, u.first_name
ORDER BY u.id;

▶ Run this query in the SQL Playground

Two details make Kenneth's row come out as 0 and 0:

The most common practical use of LEFT JOIN, and a standard interview question.

Unmatched rows are the ones whose right-table columns are NULL. Keep only those with IS NULL and you have the rows that have no partner. Which customers have never placed an order?

SELECT u.id, u.first_name, u.last_name, u.city
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
WHERE o.id IS NULL
ORDER BY u.id;

▶ Run this query in the SQL Playground

36 customers have no orders. Test a right-table column that can never be NULL in a real row, normally its primary key (o.id). If you test a column that is allowed to be NULL, a matched row with an empty value would be mistaken for a missing one.

The same pattern answers any "which X have no Y" question. Which product has never been ordered?

SELECT p.id, p.name, p.category
FROM products p
LEFT JOIN order_items oi
  ON oi.product_id = p.id
WHERE oi.id IS NULL;

▶ Run this query in the SQL Playground

LEFT JOIN … IS NULL vs NOT EXISTS vs NOT IN

-- the same question with NOT EXISTS
SELECT u.id, u.first_name
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Both forms return the same 36 customers here. See EXISTS and NOT EXISTS for the subquery version in depth.

The mistake that silently turns a LEFT JOIN into an INNER JOIN.

Suppose you want every customer with the number of delivered orders. Where does the condition o.status = 'delivered' go? It makes a large difference:

SELECT 'condition in WHERE' AS version, COUNT(DISTINCT u.id) AS customers_returned
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'delivered'
UNION ALL
SELECT 'condition in ON', COUNT(DISTINCT u.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'delivered';

▶ Run this query in the SQL Playground

With the condition in WHERE, only 65 of 150 customers survive. WHERE runs after the join, and for an unmatched customer o.status is NULL, so NULL = 'delivered' is not true and the row is removed. The LEFT JOIN has become an INNER JOIN.

With the condition in ON, it only decides which orders count as a match. Every customer is kept:

SELECT u.id, u.first_name,
       COUNT(o.id) AS delivered_orders
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
 AND o.status = 'delivered'
WHERE u.id <= 6
GROUP BY u.id, u.first_name
ORDER BY u.id;

▶ Run this query in the SQL Playground

Add one LEFT JOIN … ON per table. Each join works on the result of the previous one. Here every order is listed with its customer and any payments recorded for it:

SELECT o.id AS order_id, u.first_name, o.status,
       p.method, p.status AS payment_status
FROM orders o
LEFT JOIN users u
  ON u.id = o.user_id
LEFT JOIN payments p
  ON p.order_id = o.id
WHERE o.id <= 6
ORDER BY o.id, p.id;

▶ Run this query in the SQL Playground

Orders 4 and 6 have no payment, so their payment columns are NULL, but the orders are not lost. Orders 2, 3 and 5 have two payments each and appear twice.

Do not put an INNER JOIN after a LEFT JOIN by accident

A later INNER JOIN that depends on the right table throws away the NULL rows the LEFT JOIN kept:

SELECT 'LEFT JOIN, then LEFT JOIN' AS version, COUNT(DISTINCT u.id) AS customers
FROM users u
LEFT JOIN orders o   ON o.user_id = u.id
LEFT JOIN payments p ON p.order_id = o.id
UNION ALL
SELECT 'LEFT JOIN, then INNER JOIN', COUNT(DISTINCT u.id)
FROM users u
LEFT JOIN orders o    ON o.user_id = u.id
INNER JOIN payments p ON p.order_id = o.id;

▶ Run this query in the SQL Playground

The second version keeps only 89 customers: the ones with at least one paid order. If the first table must stay complete, every join after it has to be a LEFT JOIN too.

A LEFT JOIN does not create duplicates on its own. A left row is repeated because it has several matches, which is correct for a one-to-many relationship. The trouble starts when you join two "many" tables to the same parent, such as a customer's orders and a customer's reviews. Every order is paired with every review:

SELECT u.id,
       COUNT(DISTINCT o.id) AS orders,
       COUNT(DISTINCT r.id) AS reviews,
       COUNT(*)             AS joined_rows,
       ROUND(SUM(o.total), 2) AS inflated_total
FROM users u
LEFT JOIN orders o  ON o.user_id = u.id
LEFT JOIN reviews r ON r.user_id = u.id
GROUP BY u.id
HAVING COUNT(DISTINCT o.id) > 1 AND COUNT(DISTINCT r.id) > 1
ORDER BY joined_rows DESC, u.id
LIMIT 3;

▶ Run this query in the SQL Playground

Customer 36 has 3 orders and 4 reviews, which become 12 joined rows, so SUM(o.total) adds every order 4 times. The fix is to summarise each child table first in a subquery, then LEFT JOIN the summaries, which have one row per customer:

SELECT u.id, u.first_name,
       COALESCE(o.orders, 0)  AS orders,
       COALESCE(o.spent, 0)   AS spent,
       COALESCE(r.reviews, 0) AS reviews
FROM users u
LEFT JOIN (
  SELECT user_id, COUNT(*) AS orders, ROUND(SUM(total), 2) AS spent
  FROM orders
  GROUP BY user_id
) AS o ON o.user_id = u.id
LEFT JOIN (
  SELECT user_id, COUNT(*) AS reviews
  FROM reviews
  GROUP BY user_id
) AS r ON r.user_id = u.id
WHERE u.id IN (11, 15, 43)
ORDER BY u.id;

▶ Run this query in the SQL Playground

A RIGHT JOIN keeps every row of the table written second. Swap the table order and it is the same query:

SELECT 'users LEFT JOIN orders' AS query, COUNT(*) AS rows_returned
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
UNION ALL
SELECT 'orders RIGHT JOIN users', COUNT(*)
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;

▶ Run this query in the SQL Playground

Most teams write LEFT JOIN only, so the table being kept is always the first one you read. To keep unmatched rows from both sides, use a FULL OUTER JOIN.

In employees, manager_id points at another employee. Joining the table to itself lists each person with their manager, and LEFT JOIN keeps the people at the top who have none:

SELECT e.id, e.first_name, e.title,
       m.first_name AS manager
FROM employees e
LEFT JOIN employees m
  ON m.id = e.manager_id
WHERE e.id BETWEEN 8 AND 13
ORDER BY e.id;

▶ Run this query in the SQL Playground

The two aliases, e and m, let one table play both roles. The SELF JOIN lesson covers this pattern.

Most business uses are one of two shapes: "everything, with details where they exist" or "the ones with nothing". The tables change; the query does not.

BI tools use the same join under different names: "Left Outer" in Power Query and Excel merges, and the left join type in Tableau.

Five errors that return wrong results without any error message.

SELECT u.id, o.id
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
WHERE o.status = 'delivered';

SELECT u.id, o.id
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
 AND o.status = 'delivered';

SELECT u.id
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
WHERE o.id = NULL;

SELECT u.id
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
WHERE o.id IS NULL;

SELECT u.id, COUNT(*) AS orders
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
GROUP BY u.id;

SELECT u.id, COUNT(o.id) AS orders
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
GROUP BY u.id;

SELECT u.first_name, o.id
FROM orders o
LEFT JOIN users u
  ON u.id = o.user_id;

SELECT u.first_name, o.id
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id;

SELECT u.id, SUM(o.total)
FROM users u
LEFT JOIN orders o  ON o.user_id = u.id
LEFT JOIN reviews r ON r.user_id = u.id
GROUP BY u.id;

SELECT u.id, o.spent
FROM users u
LEFT JOIN (
  SELECT user_id, SUM(total) AS spent
  FROM orders GROUP BY user_id
) AS o ON o.user_id = u.id;

On the small Playground tables every query is instant. These points matter once tables reach hundreds of thousands of rows.

8 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy List the first ten customers by id with the id and status of each of their orders. Customers with no orders must still appear. Table: users, orders

   Show solution

   SQLite · runs in the Playground

   SELECT u.id, u.first_name, o.id AS order_id, o.status
   FROM users u
   LEFT JOIN orders o
     ON o.user_id = u.id
   WHERE u.id <= 10
   ORDER BY u.id, o.id;

   ▶ Run this query in the SQL Playground

   The filter is on the left table, so it is safe in WHERE. The result has 18 rows because some customers have several orders and some have none.

2. 2Easy Find the products that have no reviews. Show the id, name and category. Table: products, reviews

   Show solution

   SQLite · runs in the Playground

   SELECT p.id, p.name, p.category
   FROM products p
   LEFT JOIN reviews r
     ON r.product_id = p.id
   WHERE r.id IS NULL
   ORDER BY p.id;

   ▶ Run this query in the SQL Playground

   LEFT JOIN keeps every product and r.id IS NULL keeps only the unmatched ones: 27 products.

3. 3Medium Show every customer with the number of orders they have placed, highest first. Customers with none should show 0. Table: users, orders

   Show solution

   SQLite · runs in the Playground

   SELECT u.id, u.first_name, u.last_name,
          COUNT(o.id) AS orders
   FROM users u
   LEFT JOIN orders o
     ON o.user_id = u.id
   GROUP BY u.id, u.first_name, u.last_name
   ORDER BY orders DESC, u.id;

   ▶ Run this query in the SQL Playground

   COUNT(o.id) skips the NULL of an unmatched customer, so they score 0. All 150 customers are returned.

4. 4Medium Which orders have no payment recorded? Show the order id, status and total, largest total first. Table: orders, payments

   Show solution

   SQLite · runs in the Playground

   SELECT o.id, o.status, o.total
   FROM orders o
   LEFT JOIN payments p
     ON p.order_id = o.id
   WHERE p.id IS NULL
   ORDER BY o.total DESC;

   ▶ Run this query in the SQL Playground

   The anti-join pattern again, with orders as the table to keep. 75 orders have no payment row.

5. 5Medium For each product category, count the products that have no reviews. Table: products, reviews

   Show solution

   SQLite · runs in the Playground

   SELECT p.category,
          COUNT(*) AS products_without_reviews
   FROM products p
   LEFT JOIN reviews r
     ON r.product_id = p.id
   WHERE r.id IS NULL
   GROUP BY p.category
   ORDER BY products_without_reviews DESC, p.category;

   ▶ Run this query in the SQL Playground

   After the IS NULL filter each remaining row is one unreviewed product, so COUNT(*) is correct here.

6. 6Medium Show every customer with the number of cancelled orders they have, including customers with none. Return the ten customers with the most. Table: users, orders

   Show solution

   SQLite · runs in the Playground

   SELECT u.id, u.first_name,
          COUNT(o.id) AS cancelled_orders
   FROM users u
   LEFT JOIN orders o
     ON o.user_id = u.id
    AND o.status = 'cancelled'
   GROUP BY u.id, u.first_name
   ORDER BY cancelled_orders DESC, u.id
   LIMIT 10;

   ▶ Run this query in the SQL Playground

   The status condition is in ON, so it limits which orders match without removing customers. In WHERE it would drop everyone with no cancelled order.

7. 7Challenging For each department, count the employees who earn more than 150,000. Every department must appear, even if the count is 0. Table: departments, employees

   Show solution

   SQLite · runs in the Playground

   SELECT d.name,
          COUNT(e.id) AS high_earners
   FROM departments d
   LEFT JOIN employees e
     ON e.department_id = d.id
    AND e.salary > 150000
   GROUP BY d.id, d.name
   ORDER BY high_earners DESC, d.name;

   ▶ Run this query in the SQL Playground

   departments is the left table because the report is "per department". The salary condition belongs in ON for the same reason as in the previous exercise.

8. 8Challenging Find customers who have placed at least one order but have never written a review. Show each customer once. Table: users, orders, reviews

   Show solution

   SQLite · runs in the Playground

   SELECT DISTINCT u.id, u.first_name, u.last_name
   FROM users u
   INNER JOIN orders o
     ON o.user_id = u.id
   LEFT JOIN reviews r
     ON r.user_id = u.id
   WHERE r.id IS NULL
   ORDER BY u.id;

   ▶ Run this query in the SQL Playground

   INNER JOIN keeps only customers with orders; the LEFT JOIN with IS NULL then removes the ones who have reviewed. DISTINCT collapses customers with several orders. 30 customers qualify.