SQL LEFT JOIN and RIGHT JOIN

LEFT JOIN returns every row from the left table, plus matching rows from the right table. When there's no match, the right table's columns are NULL. It's the join you need for questions like "all customers and their order count, including zero" or "products that have never sold".

RIGHT JOIN is the mirror image: every row from the right table.

Syntax

SELECT ...
FROM left_table l
LEFT JOIN right_table r
  ON l.key = r.key;

Examples

1. All customers, with or without orders

SELECT c.customer_name,
       o.order_id,
       o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE c.customer_id IN (11, 12);

Result:

(2 rows)

Divya Menon (customer 12) has no orders, so her order_id and status are NULL. An inner join would have dropped her.

2. Order count per customer, including zero

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

Result:

(5 rows)

Use COUNT(o.order_id), not COUNT(*). COUNT(*) would count Divya's NULL row as 1.

3. Find rows with no match (anti-join)

Products that have never been sold: LEFT JOIN, then keep rows where the right side is NULL:

SELECT p.product_id, p.product_name
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
WHERE oi.order_item_id IS NULL;

Result:

(1 row)

Customers who have never ordered:

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

Result:

(1 row)

These "never bought" lists are the starting point for re-engagement campaigns.

4. RIGHT JOIN

The same as example 3, written with the tables swapped:

SELECT p.product_id, p.product_name
FROM order_items oi
RIGHT JOIN products p ON oi.product_id = p.product_id
WHERE oi.order_item_id IS NULL;

Result:

(1 row)

Same result. Most analysts stick to LEFT JOIN and put the "keep all" table first.

The WHERE vs ON trap

Say you want every customer with their count of delivered orders. Putting the status filter in WHERE quietly turns the LEFT JOIN into an INNER JOIN, because NULL statuses fail the condition:

-- Wrong: customers with no delivered orders disappear
SELECT c.customer_name, COUNT(o.order_id) AS delivered_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'Delivered'
GROUP BY c.customer_name
ORDER BY delivered_orders, c.customer_name
LIMIT 4;

Result:

(4 rows)

Move the condition into ON, so it filters the right table before the join:

-- Right: every customer stays, with 0 where appropriate
SELECT c.customer_name, COUNT(o.order_id) AS delivered_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
                  AND o.status = 'Delivered'
GROUP BY c.customer_name
ORDER BY delivered_orders, c.customer_name
LIMIT 4;

Result:

(4 rows)

Arjun (whose only orders are cancelled or pending) and Divya (no orders at all) now appear with 0. The rule: conditions on the right table of a LEFT JOIN go in ON.

Common mistakes

• Filtering the right table in WHERE, as shown above.
• Using COUNT(*) after a LEFT JOIN, which counts unmatched rows as 1.
• Chaining an INNER JOIN after a LEFT JOIN. customers LEFT JOIN orders JOIN order_items drops customers without orders again. Keep later joins as LEFT JOINs too.

Practice question

List every category with the number of products it contains, including categories with no products. (All six categories currently have products, so every count will be above zero.)

SELECT cat.category_name,
       COUNT(p.product_id) AS products
FROM categories cat
LEFT JOIN products p ON p.category_id = cat.category_id
GROUP BY cat.category_name
ORDER BY products DESC, cat.category_name;

Key takeaways

• LEFT JOIN keeps every row from the left table; unmatched right columns are NULL.
• LEFT JOIN ... WHERE right.key IS NULL finds rows with no match.
• Put conditions on the right-hand table in ON, not WHERE.
• RIGHT JOIN is a LEFT JOIN with the tables swapped.