SQL LEFT JOIN Explained Simply (With Diagrams and Examples)

An INNER JOIN only keeps rows that match in both tables. That is often too strict. What if you want a list of all customers, and their orders if they have any? That is the job of LEFT JOIN.

A LEFT JOIN returns every row from the left table, plus the matching rows from the right table. When there is no match, the right table's columns are filled with NULL.

The example tables#

We use the same tables as the INNER JOIN lesson.

customers (the left table in our examples)

orders (the right table)

LEFT JOIN syntax#

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

"Left" simply means the table written first, the one after FROM. LEFT OUTER JOIN is exactly the same thing; the word OUTER is optional.

Your first LEFT JOIN#

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_id;

name  | order_id | amount
------+----------+-------
Asha  | 101      | 250
Asha  | 102      | 120
Ravi  | 103      | 560
Meera | NULL     | NULL
John  | NULL     | NULL

Compare this with the INNER JOIN, which returned only three rows:

Order 104 still does not appear. It is in the right table and has no matching customer, and a LEFT JOIN only protects rows from the left table.

Table order matters#

With an INNER JOIN, swapping the tables gives the same result. With a LEFT JOIN, it does not. Make orders the left table and the question changes to "every order, with its customer if one exists":

SELECT o.order_id, o.amount, c.name
FROM orders o
LEFT JOIN customers c
  ON o.customer_id = c.customer_id
ORDER BY o.order_id;

order_id | amount | name
---------+--------+-----
101      | 250    | Asha
102      | 120    | Asha
103      | 560    | Ravi
104      | 90     | NULL

Now order 104 is kept and Meera and John are gone. Always ask yourself: which table must not lose any rows? That one goes on the left.

Find rows with no match#

One of the most useful LEFT JOIN patterns is finding things that are missing, such as customers who have never ordered. Join, then keep only the rows where the right side is NULL:

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

name  | city
------+--------
Meera | Pune
John  | Chennai

This pattern is sometimes called an anti-join. It appears constantly in real work and in interviews ("find employees without a manager", "find products that never sold").

Count including zero#

Combine LEFT JOIN with GROUP BY to build reports that include zeros:

SELECT c.name,
       COUNT(o.order_id)         AS orders,
       COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name
ORDER BY total_spent DESC;

name  | orders | total_spent
------+--------+------------
Ravi  | 1      | 560
Asha  | 2      | 370
Meera | 0      | 0
John  | 0      | 0

Two details make this work:

• COUNT(o.order_id) counts only non-NULL values, so customers without orders get 0. COUNT(*) would wrongly give them 1, because their row still exists.
• SUM of nothing is NULL. COALESCE(value, 0) replaces NULL with 0.

The WHERE clause trap#

This is the most common LEFT JOIN bug, even among experienced developers. Suppose you want every customer, plus only their orders above 200:

-- Looks right, but behaves like an INNER JOIN
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.amount > 200;

name | order_id | amount
-----+----------+-------
Asha | 101      | 250
Ravi | 103      | 560

Meera and John vanished. Their o.amount is NULL, and NULL > 200 is not true, so WHERE removed them after the join. The fix is to move the condition on the right table into the ON clause:

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
 AND o.amount > 200
ORDER BY c.customer_id;

name  | order_id | amount
------+----------+-------
Asha  | 101      | 250
Ravi  | 103      | 560
Meera | NULL     | NULL
John  | NULL     | NULL

LEFT JOIN vs other joins#

• INNER JOIN: only matching rows.
• LEFT JOIN: all rows from the left table.
• RIGHT JOIN: all rows from the right table. It is a mirror image of LEFT JOIN, and most people rewrite it as a LEFT JOIN with the tables swapped.
• FULL OUTER JOIN: all rows from both tables. (MySQL does not support it directly.)

Common mistakes#

Frequently asked questions#

Is LEFT JOIN the same as LEFT OUTER JOIN?#

Yes. OUTER is optional. LEFT JOIN and LEFT OUTER JOIN return exactly the same result in every major database.

Can a LEFT JOIN return more rows than the left table has?#

Yes. If a left row matches several rows in the right table, it appears once per match. Our 4 customers produced 5 rows because Asha has two orders.

What is the difference between LEFT JOIN and RIGHT JOIN?#

They are mirror images. A LEFT JOIN keeps every row from the table written first; a RIGHT JOIN keeps every row from the table written second. Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order.

Practice#

SELECT c.name,
       c.city,
       COUNT(o.order_id) AS big_orders
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
 AND o.amount > 100
GROUP BY c.customer_id, c.name, c.city
ORDER BY c.customer_id;

name  | city    | big_orders
------+---------+-----------
Asha  | Mumbai  | 2
Ravi  | Delhi   | 1
Meera | Pune    | 0
John  | Chennai | 0

Key takeaway#