Introduction
LEFT JOIN (LEFT OUTER JOIN) returns every row from the left table, plus matching rows from the right table; when there is no match, the right table's columns are filled with NULL. RIGHT JOIN (RIGHT OUTER JOIN) is the mirror image, preserving every row from the right table. Both are used when you need to keep unmatched rows from one side visible, such as finding customers who have never placed an order.
Cricket analogy: LEFT JOIN is like listing every registered player from a squad even if some didn't bat in a match, filling their scores with NULL, useful for finding players like a reserve who never got a chance to play.
Syntax
-- LEFT JOIN: keep all customers, even those without orders
SELECT c.customer_id, c.customer_name, o.order_id, o.order_amount
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id;
-- RIGHT JOIN: keep all orders, even ones with a missing customer
SELECT c.customer_id, c.customer_name, o.order_id, o.order_amount
FROM customers AS c
RIGHT JOIN orders AS o
ON c.customer_id = o.customer_id;Explanation
The table named immediately before LEFT JOIN (or after FROM) is the 'left' table; the one after LEFT JOIN is the 'right' table. Every left-table row is guaranteed to appear at least once. If it matches one or more right-table rows, one output row is produced per match; if it matches none, a single output row is produced with all right-table columns set to NULL. RIGHT JOIN works identically but guarantees every right-table row instead. RIGHT JOIN is less commonly used because any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order, and some dialects (notably older SQLite versions) do not support RIGHT JOIN at all, so LEFT JOIN is generally preferred for portability.
Cricket analogy: In 'players LEFT JOIN innings', players is the left table so every registered player appears at least once; a player like a benched all-rounder with no innings gets one row with all innings columns NULL.
Example
-- customers
-- customer_id | customer_name
-- 1 | Alice
-- 2 | Bob
-- 3 | Carla
-- orders
-- order_id | customer_id | order_amount
-- 100 | 1 | 250
-- 101 | 1 | 75
-- 102 | 2 | 400
SELECT c.customer_name, o.order_id, o.order_amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;Output
Result rows: Alice/100/250, Alice/101/75, Bob/102/400, and Carla/NULL/NULL. Carla is preserved because LEFT JOIN keeps every left-table (customers) row, but since she has no matching orders row, order_id and order_amount are NULL-padded. Swapping to a RIGHT JOIN with customers on the left and orders on the right would instead guarantee every orders row survives (irrelevant here since every order has a valid customer_id).
Cricket analogy: Carla with NULL order data is like a squad member, say a reserve wicketkeeper, listed on the roster with all his match stats NULL because he never played a game, yet still shown since players is the left table.
Key Takeaways
- LEFT JOIN keeps all rows from the left table; unmatched right-side columns become NULL.
- RIGHT JOIN keeps all rows from the right table; unmatched left-side columns become NULL.
- Any RIGHT JOIN can be rewritten as an equivalent LEFT JOIN by swapping table order.
- LEFT JOIN is the more portable and more commonly used choice across SQL dialects.
- A LEFT JOIN followed by 'WHERE right_table.key IS NULL' is a classic pattern for finding unmatched rows (e.g., customers with no orders).
Practice what you learned
1. In a LEFT JOIN between customers and orders, what happens to a customer with no orders?
2. How can a RIGHT JOIN be rewritten using LEFT JOIN?
3. Which pattern finds customers who have never placed an order?
4. Why might a developer avoid RIGHT JOIN for portability?
Was this page helpful?
You May Also Like
INNER JOIN
Combine rows from two tables where the join condition matches in both, discarding unmatched rows.
FULL OUTER JOIN
Combine LEFT and RIGHT JOIN behavior, keeping all rows from both tables and NULL-padding unmatched sides.
Self Join
Join a table to itself using aliases to compare rows within the same table, such as employees to their managers.