LEFT and RIGHT JOIN
40 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)You want every customer with their orders, but a plain join drops customers who have not ordered yet. The report looks complete and is quietly wrong. How do you keep them?
Keeping every row from the left table
A LEFT JOIN returns every row of the left table, the one written before LEFT JOIN. Rows that match are paired as in an inner join; rows with no match get NULL in the right-table columns.
An inner join drops a category that has no products, but a report must list every category. Keep all rows of the first table.
Example 1 · Every category, even with no products
-- BeforeSELECT category_id, name FROM categories WHERE category_id IN (1, 2, 3);SELECT product_id, name, category_id FROM products WHERE category_id IN (2, 3) ORDER BY product_id;
-- After: every category, with its products or NULLsSELECT c.name AS category, p.name AS product, p.priceFROM categories cLEFT JOIN products p ON p.category_id = c.category_idWHERE c.category_id IN (1, 2, 3)ORDER BY c.category_id, p.product_id;+-------------+-------------+ | category_id | name | +-------------+-------------+ | 1 | Electronics | | 2 | Mobiles | | 3 | Laptops | +-------------+-------------+ +------------+------------------------+-------------+ | product_id | name | category_id | +------------+------------------------+-------------+ | 1 | Redmi Note 13 | 2 | | 2 | Samsung Galaxy M35 | 2 | | 3 | iPhone 15 | 2 | | 4 | Lenovo IdeaPad Slim 3 | 3 | | 5 | HP Victus Gaming | 3 | +------------+------------------------+-------------+ +-------------+------------------------+----------+ | category | product | price | +-------------+------------------------+----------+ | Electronics | NULL | NULL | | Mobiles | Redmi Note 13 | 17999.00 | | Mobiles | Samsung Galaxy M35 | 19999.00 | | Mobiles | iPhone 15 | 69900.00 | | Laptops | Lenovo IdeaPad Slim 3 | 42990.00 | | Laptops | HP Victus Gaming | 64990.00 | +-------------+------------------------+----------+
What this code does
- Mobiles has three products and Laptops has two, so they get one row per product, just like an inner join.
- Electronics has no products of its own (they sit in its sub-categories). An inner join would drop it; the left join keeps it with
NULLin bothproductscolumns.
LEFT JOIN is also written LEFT OUTER JOIN; the two mean the same. Dropping a category with no products would hide the most important line of a report such as "how is each of my categories doing?", so a left join gives at least one row for every left row: one row per match, or one row with empty right-side columns when there is none. Choose the left table by asking "whose rows must all appear?".
Fix the error
Fix the missing customer
Imran Khan has no orders, and the report must still list him.
The report must list all 10 customers.
Error
No error, but Imran Khan (customer 10) is missing from the result.
NULLs where there is no match
COALESCE(a, b) returns the first value that is not NULL. After a left join it replaces the NULL of a missing match with a default, such as 0.
When a left row has no partner, MySQL fills every right-table column with NULL in that row. NULL here means "there was no matching row", not a value stored anywhere.
Example 2 · COUNT(*) versus COUNT(o.order_id)
SELECT c.customer_id, c.name, COUNT(*) AS wrong_count, COUNT(o.order_id) AS ordersFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_idGROUP BY c.customer_id, c.nameORDER BY c.customer_id;+-------------+--------------+-------------+--------+ | customer_id | name | wrong_count | orders | +-------------+--------------+-------------+--------+ | 1 | Asha Rao | 3 | 3 | | 2 | Ravi Kumar | 3 | 3 | | 3 | Meena Iyer | 1 | 1 | | 4 | Kabir Singh | 2 | 2 | | 5 | Priya Sharma | 2 | 2 | | 6 | Arjun Mehta | 1 | 1 | | 7 | Divya Nair | 1 | 1 | | 8 | Rahul Verma | 1 | 1 | | 9 | Sneha Patil | 1 | 1 | | 10 | Imran Khan | 1 | 0 | +-------------+--------------+-------------+--------+
What this code does
- For customers with orders, both counts agree. For Imran Khan, who never ordered,
COUNT(*)says 1 because hisNULL-filled row is still a row. COUNT(o.order_id)skipsNULLs, so it gives the honest 0. Always count a right-table column after a left join.
Example 3 · COALESCE turns a missing total into 0
SELECT d.dept_name, COUNT(e.emp_id) AS staff, SUM(e.salary) AS raw_total, COALESCE(SUM(e.salary), 0) AS salary_billFROM departments dLEFT JOIN employees e ON e.dept_id = d.dept_idGROUP BY d.dept_nameORDER BY d.dept_name;+-------------+-------+-----------+-------------+ | dept_name | staff | raw_total | salary_bill | +-------------+-------+-----------+-------------+ | Engineering | 5 | 765000.00 | 765000.00 | | Finance | 2 | 215000.00 | 215000.00 | | HR | 2 | 150000.00 | 150000.00 | | Research | 0 | NULL | 0.00 | | Sales | 3 | 320000.00 | 320000.00 | +-------------+-------+-----------+-------------+
What this code does
- Research has no employees, so
SUM(e.salary)adds up nothing and returnsNULL. COALESCE(SUM(e.salary), 0)returns the first non-NULLvalue, so Research shows0.00(the 0 takes the decimal shape of the salary column) and the other departments keep their real totals.
Count the right table's primary key: it is NULL only when the match is missing.
Predict the output
Predict the left join counts
Customers 1 to 9 have orders; customer 10 has none. There are 15 orders.
Round 1 of 2
COUNT(*) counts the row itself; use COUNT(o.order_id).RIGHT JOIN and why LEFT is preferred
An outer join keeps rows that have no match. A LEFT JOIN keeps the unmatched rows of the left table, a RIGHT JOIN those of the right table, and a FULL OUTER JOIN both.
RIGHT JOIN is the mirror image: it keeps every row of the table written after it. employees e RIGHT JOIN departments d keeps every department, exactly like departments d LEFT JOIN employees e.
Example 4 · RIGHT JOIN is LEFT JOIN with the tables swapped
-- Keeps every department (the table after RIGHT JOIN)SELECT d.dept_name, e.nameFROM employees eRIGHT JOIN departments d ON e.dept_id = d.dept_idWHERE d.dept_name IN ('HR', 'Research')ORDER BY d.dept_name, e.name;
-- The same result, written the usual waySELECT d.dept_name, e.nameFROM departments dLEFT JOIN employees e ON e.dept_id = d.dept_idWHERE d.dept_name IN ('HR', 'Research')ORDER BY d.dept_name, e.name;+-----------+---------------+ | dept_name | name | +-----------+---------------+ | HR | Lakshmi Menon | | HR | Rohan Das | | Research | NULL | +-----------+---------------+ +-----------+---------------+ | dept_name | name | +-----------+---------------+ | HR | Lakshmi Menon | | HR | Rohan Das | | Research | NULL | +-----------+---------------+
What this code does
- Both queries keep Research with
NULLfor the employee: the table whose rows must all appear isdepartmentsin both. - The
WHEREhere filters the kept table (departments), so it removes no unmatched rows. That is the safe kind ofWHEREafter an outer join.
Any right join can be rewritten as a left join by swapping the two tables, so most teams use only LEFT JOIN. Then every query reads the same way: the table whose rows must all appear comes first, and everything optional is joined after it. Mixing left and right joins in one query makes it very hard to tell which rows survive.
Use RIGHT JOIN when you meet it in someone else's query; write LEFT JOIN in your own. Both are fully supported in MySQL 8.0 and later.
Conditions in ON versus WHERE
In a left join, a condition in ON decides which right rows match; a condition in WHERE filters the final rows. For an inner join the two places give the same result. For a left join they do not.
Example 5 · Trap: a right-table filter in WHERE
-- Wrong: WHERE removes every customer without a cancelled orderSELECT c.name, o.order_id AS cancelled_orderFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_idWHERE o.status = 'CANCELLED'ORDER BY c.customer_id;
-- Right: the status test decides what matches, every customer staysSELECT c.name, o.order_id AS cancelled_orderFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'CANCELLED'ORDER BY c.customer_id;+--------------+-----------------+ | name | cancelled_order | +--------------+-----------------+ | Meena Iyer | 4 | | Priya Sharma | 14 | +--------------+-----------------+ +--------------+-----------------+ | name | cancelled_order | +--------------+-----------------+ | Asha Rao | NULL | | Ravi Kumar | NULL | | Meena Iyer | 4 | | Kabir Singh | NULL | | Priya Sharma | 14 | | Arjun Mehta | NULL | | Divya Nair | NULL | | Rahul Verma | NULL | | Sneha Patil | NULL | | Imran Khan | NULL | +--------------+-----------------+
What this code does
- The first query returns only 2 customers: the others had
NULL(or another status) ino.status, andWHEREthrew those rows away. It behaves like an inner join. - In the second query the status test is part of matching. All 10 customers stay; only Meena's and Priya's rows have a cancelled order next to them.
Example 6 · Trap: a left-table filter in ON
-- Does NOT keep only Chennai customers: it only limits who can matchSELECT c.name, c.city, COUNT(o.order_id) AS ordersFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_id AND c.city = 'Chennai'GROUP BY c.customer_id, c.name, c.cityORDER BY c.customer_id;
-- Right: filter the left table in WHERESELECT c.name, c.city, COUNT(o.order_id) AS ordersFROM customers cLEFT JOIN orders o ON o.customer_id = c.customer_idWHERE c.city = 'Chennai'GROUP BY c.customer_id, c.name, c.cityORDER BY c.customer_id;+--------------+-----------+--------+ | name | city | orders | +--------------+-----------+--------+ | Asha Rao | Bengaluru | 0 | | Ravi Kumar | Chennai | 3 | | Meena Iyer | Chennai | 1 | | Kabir Singh | Delhi | 0 | | Priya Sharma | Pune | 0 | | Arjun Mehta | Mumbai | 0 | | Divya Nair | Kochi | 0 | | Rahul Verma | Delhi | 0 | | Sneha Patil | Pune | 0 | | Imran Khan | Hyderabad | 0 | +--------------+-----------+--------+ +------------+---------+--------+ | name | city | orders | +------------+---------+--------+ | Ravi Kumar | Chennai | 3 | | Meena Iyer | Chennai | 1 | +------------+---------+--------+
What this code does
- A left join keeps every left row whatever
ONsays, so the first query still lists all 10 customers. Non-Chennai customers just get 0 orders, which is wrong data, not a filter. - In
WHERE,c.city = 'Chennai'removes the other customers before grouping, leaving Ravi with 3 orders and Meena with 1.
The common trap: LEFT JOIN orders o ON ... WHERE o.status = 'CANCELLED'. Customers without a cancelled order get NULL in o.status, NULL = 'CANCELLED' is not true, and WHERE removes them. The left join has quietly become an inner join. Put the condition in ON (ON o.customer_id = c.customer_id AND o.status = 'CANCELLED') to keep every customer. The opposite mistake also exists. A condition on the left table inside ON (AND c.city = 'Chennai') does not remove any customers; it only stops non-Chennai customers from matching. To keep only Chennai customers, filter the left table in WHERE.
- Condition on the right table, all left rows wanted → put it in
ON. - Condition on the left table, to drop left rows → put it in
WHERE. WHERE right_column = valueafter a left join → it has become an inner join.
Predict the output
Predict: ON versus WHERE
Orders 4 and 14 are the only CANCELLED ones (customers 3 and 5).
Round 1 of 2
Next step
Check your understanding
12 quick questions