In the INNER JOIN chapter we saw that an inner join silently drops every row that has no partner. Siddharth Rao never placed an order, so he vanished from the order report. The Research department has no employees, so it vanished from the headcount report. Order 1008 was a guest order with no customer, so it vanished too.
Very often those "missing" rows are exactly what the business wants to see: customers who never ordered (send them a coupon), departments with zero staff (hire), orders without a valid customer (data problem). Outer joins keep unmatched rows and fill the missing side with NULL. In this chapter we cover the LEFT, RIGHT and FULL outer joins, the anti-join pattern, correct counting with outer joins, and the famous ON-versus-WHERE trap.
All examples use the shared company_db sample database, and every output shown is real MySQL 8.4 output.
What is an Outer Join?
An outer join returns all the matched row pairs (exactly like an inner join) plus the unmatched rows of one or both tables. In those extra rows, every column of the table that had no match is filled with NULL.
There are three kinds, depending on whose unmatched rows are kept:
Join | Result |
|---|---|
| Matched pairs + unmatched rows of A (the left table) |
| Matched pairs + unmatched rows of B (the right table) |
| Matched pairs + unmatched rows of A + unmatched rows of B |
"Left" and "right" simply mean the table written before and after the JOIN keyword. The word OUTER is optional: LEFT JOIN and LEFT OUTER JOIN are identical.
LEFT JOIN
SELECT columns
FROM left_table l
LEFT [OUTER] JOIN right_table r
ON join_condition;The rule: every row of the left table appears in the result at least once. If it has matches, it appears once per match (as in an inner join). If it has none, it appears once with NULLs in all right-table columns.
All customers with their orders, including customers who never ordered:
SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;+-------------+---------------+----------+---------+
| customer_id | name | order_id | amount |
+-------------+---------------+----------+---------+
| 101 | Rahul Verma | 1001 | 2500.00 |
| 101 | Rahul Verma | 1003 | 1200.00 |
| 101 | Rahul Verma | 1007 | 5600.00 |
| 102 | Kavya Menon | 1002 | 4800.00 |
| 102 | Kavya Menon | 1006 | 3100.00 |
| 103 | Aditya Joshi | 1004 | 7300.00 |
| 104 | Neha Agarwal | 1005 | 950.00 |
| 105 | Siddharth Rao | NULL | NULL |
+-------------+---------------+----------+---------+
8 rows in set (0.00 sec)Trace it from the left table's point of view:
customer matching orders output rows
---------------- ----------------------- ----------------------------
101 Rahul Verma 1001, 1003, 1007 3 (one per order)
102 Kavya Menon 1002, 1006 2
103 Aditya Joshi 1004 1
104 Neha Agarwal 1005 1
105 Siddharth Rao none 1 (order columns = NULL) <- kept!
--
8
Order 1008 (customer_id NULL) matches no customer.
It is a row of the RIGHT table, so a LEFT JOIN does not keep it.The inner join gave 7 rows; the left join gives 8. The extra row is Siddharth. Order 1008 is still missing, because the left join only protects rows of the left table.
The same idea with departments on the left: every department, including the empty one.
SELECT d.dept_id, d.dept_name, e.name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
ORDER BY d.dept_id, e.emp_id;+---------+-------------+--------------+
| dept_id | dept_name | name |
+---------+-------------+--------------+
| 10 | Engineering | Aarav Sharma |
| 10 | Engineering | Priya Iyer |
| 10 | Engineering | Rohan Mehta |
| 20 | Sales | Sneha Kapoor |
| 20 | Sales | Vikram Singh |
| 20 | Sales | Ananya Das |
| 30 | HR | Karan Patel |
| 30 | HR | Meera Nair |
| 40 | Marketing | Arjun Reddy |
| 50 | Research | NULL |
+---------+-------------+--------------+
10 rows in set (0.00 sec)Table order matters for outer joins. departments LEFT JOIN employees keeps Research; employees LEFT JOIN departments would instead keep Ishita (the employee without a department) and drop Research. Before writing an outer join, decide which table must be complete, and put it on the left.
Replacing NULLs for Display
The NULLs produced by an outer join are ordinary NULLs, so the functions from the NULL Values chapter work on them:
SELECT e.name, d.dept_name,
COALESCE(d.dept_name, 'Not assigned') AS department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
ORDER BY e.emp_id;+--------------+-------------+--------------+
| name | dept_name | department |
+--------------+-------------+--------------+
| Aarav Sharma | Engineering | Engineering |
| Priya Iyer | Engineering | Engineering |
| Rohan Mehta | Engineering | Engineering |
| Sneha Kapoor | Sales | Sales |
| Vikram Singh | Sales | Sales |
| Ananya Das | Sales | Sales |
| Karan Patel | HR | HR |
| Meera Nair | HR | HR |
| Arjun Reddy | Marketing | Marketing |
| Ishita Gupta | NULL | Not assigned |
+--------------+-------------+--------------+
10 rows in set (0.00 sec)Finding Rows With No Match (Anti-Join)
One of the most useful patterns in SQL: LEFT JOIN, then keep only the rows where the right side is NULL. Those are exactly the left rows that found no partner. This is called an anti-join.
Customers who have never placed an order:
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;+-------------+---------------+
| customer_id | name |
+-------------+---------------+
| 105 | Siddharth Rao |
+-------------+---------------+
1 row in set (0.00 sec)Departments without any employee:
SELECT d.dept_name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;+-----------+
| dept_name |
+-----------+
| Research |
+-----------+
1 row in set (0.00 sec)Test a Column That Can Never Be NULL
In the IS NULL test, use a right-table column that is never NULL in a real row, ideally the primary key or the join column. Otherwise genuine NULL values get mixed up with "no match". Watch what happens if we test e.email instead:
SELECT d.dept_name, e.name, e.email
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
WHERE e.email IS NULL;+-----------+------------+-------+
| dept_name | name | email |
+-----------+------------+-------+
| HR | Meera Nair | NULL |
| Research | NULL | NULL |
+-----------+------------+-------+
2 rows in set (0.00 sec)HR shows up because Meera matched but simply has no email. Testing e.emp_id IS NULL (a primary key, never NULL in a real row) gives only Research, which is the correct answer.
The same question can also be answered with NOT EXISTS or NOT IN subqueries, covered in the Subqueries and Correlated Subqueries & EXISTS chapters. MySQL's optimizer often turns all of these into the same anti-join plan.
Counting and Summing Correctly with LEFT JOIN
The classic report: headcount per department, including departments with 0 employees. A LEFT JOIN keeps Research, but look at the two ways of counting:
SELECT d.dept_name,
COUNT(*) AS wrong_count,
COUNT(e.emp_id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_id
ORDER BY d.dept_id;+-------------+-------------+-----------+
| dept_name | wrong_count | headcount |
+-------------+-------------+-----------+
| Engineering | 3 | 3 |
| Sales | 3 | 3 |
| HR | 2 | 2 |
| Marketing | 1 | 1 |
| Research | 1 | 0 |
+-------------+-------------+-----------+
5 rows in set (0.00 sec)Research has one joined row (the one filled with NULLs). COUNT(*) counts rows, so it says 1. COUNT(e.emp_id) counts non-NULL values, so it correctly says 0. After a LEFT JOIN, count a column of the right table, not *.
SUM, AVG, MIN and MAX return NULL for a group whose values are all NULL. Wrap them in COALESCE when you want 0:
SELECT c.name,
COUNT(o.order_id) AS num_orders,
SUM(o.amount) AS total,
COALESCE(SUM(o.amount), 0) AS total_fixed
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY c.customer_id;+---------------+------------+---------+-------------+
| name | num_orders | total | total_fixed |
+---------------+------------+---------+-------------+
| Rahul Verma | 3 | 9300.00 | 9300.00 |
| Kavya Menon | 2 | 7900.00 | 7900.00 |
| Aditya Joshi | 1 | 7300.00 | 7300.00 |
| Neha Agarwal | 1 | 950.00 | 950.00 |
| Siddharth Rao | 0 | NULL | 0.00 |
+---------------+------------+---------+-------------+
5 rows in set (0.00 sec)The Big Trap: ON vs WHERE in a LEFT JOIN
For inner joins, a filter gives the same result in ON or in WHERE. For outer joins it does not, and this is one of the most common bugs in real SQL code (and a favourite interview question).
Task: "list all customers with their delivered orders". Attempt 1 puts the status filter in WHERE:
SELECT c.name, o.order_id, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'Delivered'
ORDER BY c.customer_id, o.order_id;+--------------+----------+-----------+
| name | order_id | status |
+--------------+----------+-----------+
| Rahul Verma | 1001 | Delivered |
| Kavya Menon | 1002 | Delivered |
| Kavya Menon | 1006 | Delivered |
| Aditya Joshi | 1004 | Delivered |
+--------------+----------+-----------+
4 rows in set (0.00 sec)Neha and Siddharth are gone, even though we used a LEFT JOIN. Why? Remember the order: the join runs first, then WHERE. The LEFT JOIN correctly produced Siddharth's row with o.status = NULL. Then WHERE checked NULL = 'Delivered', got UNKNOWN, and threw the row away. Neha's only order is "Shipped", so all her rows were thrown away too. A WHERE condition on the right table that rejects NULL effectively turns the LEFT JOIN back into an INNER JOIN.
Attempt 2 puts the status filter in ON:
SELECT c.name, o.order_id, o.status
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
AND o.status = 'Delivered'
ORDER BY c.customer_id, o.order_id;+---------------+----------+-----------+
| name | order_id | status |
+---------------+----------+-----------+
| Rahul Verma | 1001 | Delivered |
| Kavya Menon | 1002 | Delivered |
| Kavya Menon | 1006 | Delivered |
| Aditya Joshi | 1004 | Delivered |
| Neha Agarwal | NULL | NULL |
| Siddharth Rao | NULL | NULL |
+---------------+----------+-----------+
6 rows in set (0.00 sec)Now the condition is part of "what counts as a match". Neha's shipped order is not a match, so she ends up with no match and the LEFT JOIN keeps her with NULLs, exactly as it does for Siddharth.
Condition in ON : decides which right rows MATCH. Left rows are always kept.
Condition in WHERE : filters the FINAL rows. It can remove left rows too,
including the NULL-filled ones the outer join just added.Rules of thumb for a LEFT JOIN:
Conditions on the right table that restrict which rows count as matches go in ON.
Conditions on the left table (which left rows you want at all) go in WHERE.
The one right-table condition that belongs in WHERE is
IS NULL, for the anti-join.
Put together with GROUP BY, this answers "delivered orders per customer, including zeros":
SELECT c.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_id
ORDER BY c.customer_id;+---------------+------------------+
| name | delivered_orders |
+---------------+------------------+
| Rahul Verma | 1 |
| Kavya Menon | 2 |
| Aditya Joshi | 1 |
| Neha Agarwal | 0 |
| Siddharth Rao | 0 |
+---------------+------------------+
5 rows in set (0.00 sec)RIGHT JOIN
A RIGHT JOIN is the mirror image: every row of the right table is kept. All orders, with the customer name where one exists:
SELECT o.order_id, o.amount, c.name
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;+----------+---------+--------------+
| order_id | amount | name |
+----------+---------+--------------+
| 1001 | 2500.00 | Rahul Verma |
| 1002 | 4800.00 | Kavya Menon |
| 1003 | 1200.00 | Rahul Verma |
| 1004 | 7300.00 | Aditya Joshi |
| 1005 | 950.00 | Neha Agarwal |
| 1006 | 3100.00 | Kavya Menon |
| 1007 | 5600.00 | Rahul Verma |
| 1008 | 800.00 | NULL |
+----------+---------+--------------+
8 rows in set (0.00 sec)The guest order 1008 is now kept, and Siddharth (an unmatched row of the left table) is dropped. Any right join can be rewritten as a left join by swapping the two tables:
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 |
+----------+---------+--------------+
| 1001 | 2500.00 | Rahul Verma |
| 1002 | 4800.00 | Kavya Menon |
| 1003 | 1200.00 | Rahul Verma |
| 1004 | 7300.00 | Aditya Joshi |
| 1005 | 950.00 | Neha Agarwal |
| 1006 | 3100.00 | Kavya Menon |
| 1007 | 5600.00 | Rahul Verma |
| 1008 | 800.00 | NULL |
+----------+---------+--------------+
8 rows in set (0.00 sec)Identical. Because of this, most developers write only LEFT JOINs: queries read naturally from the "main" table on the left, and mixing left and right joins in one query makes it hard to see which rows are protected. (MySQL itself internally converts right joins into left joins.) Still, know RIGHT JOIN well; it appears in exams and interviews.
FULL OUTER JOIN
A FULL OUTER JOIN keeps unmatched rows from both tables. For customers and orders it should show Siddharth (no orders) and order 1008 (no customer). PostgreSQL, SQL Server and Oracle support it directly. MySQL does not:
SELECT c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN orders o ON o.customer_id = c.customer_id' at line 3Emulating FULL OUTER JOIN in MySQL
The idea: FULL = LEFT + the extra rows of RIGHT. We glue two query results together with UNION (the UNION & Set Operations chapter explains UNION in detail; for now, it stacks the rows of two SELECTs and removes duplicate rows).
Method 1: LEFT JOIN UNION RIGHT JOIN
SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
UNION
SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY order_id IS NULL, order_id;+-------------+---------------+----------+---------+
| customer_id | name | order_id | amount |
+-------------+---------------+----------+---------+
| 101 | Rahul Verma | 1001 | 2500.00 |
| 102 | Kavya Menon | 1002 | 4800.00 |
| 101 | Rahul Verma | 1003 | 1200.00 |
| 103 | Aditya Joshi | 1004 | 7300.00 |
| 104 | Neha Agarwal | 1005 | 950.00 |
| 102 | Kavya Menon | 1006 | 3100.00 |
| 101 | Rahul Verma | 1007 | 5600.00 |
| NULL | NULL | 1008 | 800.00 |
| 105 | Siddharth Rao | NULL | NULL |
+-------------+---------------+----------+---------+
9 rows in set (0.00 sec)The left join contributes 8 rows (7 matches + Siddharth), the right join contributes 8 rows (7 matches + order 1008), and UNION removes the 7 matched rows that appear in both, giving 9. (The ORDER BY order_id IS NULL, order_id just puts the row with no order last.)
The weakness: UNION removes all duplicate rows, including genuine ones. If the selected columns could legitimately produce two identical rows (for example if you selected only c.name and o.amount, and a customer had two orders of the same amount), Method 1 would wrongly merge them.
Method 2: LEFT JOIN UNION ALL anti-join (safer)
SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
UNION ALL
SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL
ORDER BY order_id IS NULL, order_id;+-------------+---------------+----------+---------+
| customer_id | name | order_id | amount |
+-------------+---------------+----------+---------+
| 101 | Rahul Verma | 1001 | 2500.00 |
| 102 | Kavya Menon | 1002 | 4800.00 |
| 101 | Rahul Verma | 1003 | 1200.00 |
| 103 | Aditya Joshi | 1004 | 7300.00 |
| 104 | Neha Agarwal | 1005 | 950.00 |
| 102 | Kavya Menon | 1006 | 3100.00 |
| 101 | Rahul Verma | 1007 | 5600.00 |
| NULL | NULL | 1008 | 800.00 |
| 105 | Siddharth Rao | NULL | NULL |
+-------------+---------------+----------+---------+
9 rows in set (0.00 sec)The first half returns everything a LEFT JOIN returns. The second half returns only right rows with no match (the anti-join filter c.customer_id IS NULL), so nothing is counted twice and no de-duplication is needed. UNION ALL keeps every row and is also faster, because it skips the duplicate-removal step.
Chaining Joins: Once LEFT, Stay LEFT
When a query has several joins, an INNER JOIN placed after a LEFT JOIN can undo it. Here we want Marketing and Research with each employee's manager:
SELECT d.dept_name, e.name AS employee, m.name AS manager
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
INNER JOIN employees m ON e.manager_id = m.emp_id
WHERE d.dept_id IN (40, 50);+-----------+-------------+--------------+
| dept_name | employee | manager |
+-----------+-------------+--------------+
| Marketing | Arjun Reddy | Aarav Sharma |
+-----------+-------------+--------------+
1 row in set (0.00 sec)Research disappeared. The LEFT JOIN gave Research a row with e.manager_id = NULL, and the following INNER JOIN could not match NULL, so it dropped the row. Making the second join a LEFT JOIN as well fixes it:
SELECT d.dept_name, e.name AS employee, m.name AS manager
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
LEFT JOIN employees m ON e.manager_id = m.emp_id
WHERE d.dept_id IN (40, 50);+-----------+-------------+--------------+
| dept_name | employee | manager |
+-----------+-------------+--------------+
| Marketing | Arjun Reddy | Aarav Sharma |
| Research | NULL | NULL |
+-----------+-------------+--------------+
2 rows in set (0.00 sec)(Joining employees to itself as m is a self join, the topic of the next chapter.) Notice also that WHERE d.dept_id IN (40, 50) is a condition on the left table, so it is correctly placed in WHERE.
Comparison of All Join Types
For customers c ... JOIN orders o ON o.customer_id = c.customer_id:
Join | Rows | Siddharth (no orders) | Order 1008 (no customer) |
|---|---|---|---|
INNER JOIN | 7 | dropped | dropped |
LEFT JOIN | 8 | kept (order columns NULL) | dropped |
RIGHT JOIN | 8 | dropped | kept (customer columns NULL) |
FULL OUTER JOIN (emulated) | 9 | kept | kept |
A handy check: rows(LEFT) = rows(INNER) + unmatched left rows, and rows(FULL) = rows(INNER) + unmatched left rows + unmatched right rows = 7 + 1 + 1 = 9.
Common Mistakes
Filtering the right table in WHERE (
WHERE o.status = 'Delivered'), which silently turns the LEFT JOIN into an INNER JOIN. Put such conditions in ON.COUNT(*) after a LEFT JOIN, which counts 1 for groups with no match. Count a right-table column such as its primary key.
SUM returning NULL for groups without matches. Use
COALESCE(SUM(...), 0).Testing a nullable column in an anti-join. Test the right table's primary key or join column.
Wrong table on the left. The table whose rows must all appear goes on the left of a LEFT JOIN.
An INNER JOIN after a LEFT JOIN that removes the NULL-filled rows again.
Writing FULL OUTER JOIN in MySQL. It is a syntax error; emulate it with UNION / UNION ALL.
Interview Questions
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only matched rows. LEFT JOIN returns all rows of the left table; when a left row has no match, the right table's columns are NULL.
What is the difference between LEFT JOIN and LEFT OUTER JOIN?
None. OUTER is an optional keyword.
How do you find records in one table that have no match in another?
LEFT JOIN the second table and filter WHERE right_table.primary_key IS NULL (anti-join). NOT EXISTS is an alternative.
Why did my LEFT JOIN behave like an INNER JOIN?
Most likely a WHERE condition on a right-table column (e.g. WHERE o.status = 'X') rejects the NULL-filled rows. Move that condition into the ON clause, or check for an INNER JOIN later in the chain.
Does MySQL support FULL OUTER JOIN? How do you emulate it?
No. Use LEFT JOIN ... UNION ... RIGHT JOIN, or more safely LEFT JOIN ... UNION ALL ... RIGHT JOIN ... WHERE left.key IS NULL.
Can every RIGHT JOIN be written as a LEFT JOIN?
Yes, by swapping the order of the two tables. A RIGHT JOIN B equals B LEFT JOIN A.
If table A has 5 rows and B has 8, how many rows can A LEFT JOIN B return?
At least 5 (every A row appears at least once), and at most 5 × 8 = 40 (if every A row matches every B row).
Why is COUNT(*) wrong after a LEFT JOIN for groups without matches?
The NULL-filled row is still a row, so COUNT(*) returns 1. COUNT(right_table.column) ignores NULLs and returns 0.
Summary
Outer joins return matched pairs plus unmatched rows, filling the missing side with NULL.
LEFT JOIN keeps every left row; RIGHT JOIN keeps every right row; FULL JOIN keeps both.
OUTERis optional; table order matters for outer joins.Anti-join:
LEFT JOIN ... WHERE right.pk IS NULLfinds rows without a match.After a LEFT JOIN, use
COUNT(right.col)andCOALESCE(SUM(...), 0).Right-table filters go in ON; WHERE filters on them turn the join into an inner join.
Prefer LEFT JOIN over RIGHT JOIN for readability.
MySQL has no FULL OUTER JOIN: emulate it with UNION, or better with UNION ALL + anti-join.
So far every join has combined two different tables. In the next chapter, SELF JOIN & CROSS JOIN, we join a table with itself (employees and their managers) and look at the join that has no condition at all.