SQL RIGHT JOIN
Chapter 23 of 40 · Estimated read time: 6 min
RIGHT JOIN is the mirror of LEFT JOIN. It returns every row from the right table, plus matching rows from the left table. When there is no match in the left table, the left-table columns return NULL.
matched
left
left
ALL rows
RIGHT
RIGHT
Right table: all rows kept.
Left table: only matched rows (NULLs for unmatched).
Left table: only matched rows (NULLs for unmatched).
SELECT t1.col, t2.col
FROM table1 AS t1
RIGHT JOIN table2 AS t2 ON t1.key = t2.key;
-- All rows from t2 kept; t1 columns = NULL where no match
Dataset context: We add product P08 — Standing Desk (Furniture, ₹12,999) which has not been ordered yet. This makes the RIGHT JOIN difference visible when joining orders to products.
Example 1 — All Products, Orders Optional
-- Show every product even if it has no orders SELECT p.product_name, p.category, o.order_id, o.total_amount FROM orders AS o RIGHT JOIN products AS p ON o.product_id = p.product_id ORDER BY p.product_id;
Standing Desk (P08) appears with NULL — it has never been ordered
| product_name | category | order_id | total_amount |
|---|---|---|---|
| Wireless Mouse | Electronics | 1001 | 899 |
| USB-C Hub | Electronics | 1002 | 1499 |
| Notebook A5 | Stationery | 1003 | 495 |
| Ergonomic Chair | Furniture | 1004 | 8999 |
| Desk Lamp | Furniture | 1005 | 2398 |
| HDMI Cable | Electronics | 1006 | 299 |
| Ballpoint Pens | Stationery | 1007 | 490 |
| Standing Desk | Furniture | NULL | NULL |
Example 2 — Products Never Ordered (Anti-Join)
-- Find products that have no orders at all SELECT p.product_id, p.product_name, p.price FROM orders AS o RIGHT JOIN products AS p ON o.product_id = p.product_id WHERE o.order_id IS NULL;
Only products with no matching order row
| product_id | product_name | price |
|---|---|---|
| P08 | Standing Desk | 12999 |
RIGHT JOIN = Swapped LEFT JOIN
Every RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table positions. The results are identical — just the order of table names changes:
-- RIGHT JOIN (orders on left, products on right — keep all products) SELECT p.product_name, o.order_id FROM orders AS o RIGHT JOIN products AS p ON o.product_id = p.product_id; -- Equivalent LEFT JOIN (products on left — keep all products) SELECT p.product_name, o.order_id FROM products AS p LEFT JOIN orders AS o ON p.product_id = o.product_id; -- Both return identical results.
Practical advice: Most SQL developers always use LEFT JOIN and put the "keep all rows" table on the left. This avoids mixing LEFT and RIGHT JOIN in the same query, which makes the logic harder to follow. RIGHT JOIN is not wrong — it's just less common in practice.
Example 3 — Order Count per Product (Including Zero)
SELECT p.product_name, COUNT(o.order_id) AS times_ordered, COALESCE(SUM(o.total_amount), 0) AS revenue FROM orders AS o RIGHT JOIN products AS p ON o.product_id = p.product_id GROUP BY p.product_id, p.product_name ORDER BY revenue DESC;
| product_name | times_ordered | revenue |
|---|---|---|
| Ergonomic Chair | 1 | 8999 |
| Desk Lamp | 1 | 2398 |
| USB-C Hub | 1 | 1499 |
| Wireless Mouse | 1 | 899 |
| Notebook A5 | 1 | 495 |
| Ballpoint Pens | 1 | 490 |
| HDMI Cable | 1 | 299 |
| Standing Desk | 0 | 0 |
COUNT(o.order_id) correctly returns 0 for Standing Desk because it counts non-NULL values only. COALESCE(SUM(...), 0) turns the NULL sum into 0 for display.
When to use RIGHT JOIN
Use RIGHT JOIN when:
• The table you want to keep all rows from is already on the right side of the query
• You are adding a join to an existing query and don't want to rearrange the FROM clause
• You prefer to read it as "join X to Y, keeping all of Y"
In all other cases, rewriting as LEFT JOIN with the tables swapped is cleaner.
• The table you want to keep all rows from is already on the right side of the query
• You are adding a join to an existing query and don't want to rearrange the FROM clause
• You prefer to read it as "join X to Y, keeping all of Y"
In all other cases, rewriting as LEFT JOIN with the tables swapped is cleaner.
✏️ Practice Exercises
1. Rewrite the Example 1 RIGHT JOIN as a LEFT JOIN with tables swapped — verify the result is the same.
2. Find products in the Furniture category that have never been ordered.
3. Using RIGHT JOIN, show all customers and their orders. Then rewrite it as a LEFT JOIN.
Show Answers
-- 1. LEFT JOIN equivalent of Example 1 SELECT p.product_name, p.category, o.order_id, o.total_amount FROM products AS p LEFT JOIN orders AS o ON p.product_id = o.product_id ORDER BY p.product_id; -- 2. SELECT p.product_name, p.price FROM orders AS o RIGHT JOIN products AS p ON o.product_id = p.product_id WHERE o.order_id IS NULL AND p.category = 'Furniture'; -- 3. RIGHT JOIN version SELECT c.customer_name, o.order_id FROM orders AS o RIGHT JOIN customers AS c ON o.customer_id = c.customer_id; -- LEFT JOIN version (same result) SELECT c.customer_name, o.order_id FROM customers AS c LEFT JOIN orders AS o ON c.customer_id = o.customer_id;