Explain INNER, LEFT, RIGHT, and FULL OUTER JOIN with examples

Given two tables:

-- customers: id, name
-- (1, 'Alice'), (2, 'Bob'), (3, 'Carol')

-- orders: id, customer_id, total
-- (101, 1, 50), (102, 1, 75), (103, 2, 20)
-- Note: Carol (id 3) has no orders, and there's no order for a nonexistent customer_id 9.

INNER JOIN — only matching rows

SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

Result: Alice/50, Alice/75, Bob/20 — Carol is excluded entirely because she has no matching order row.

LEFT JOIN — all of the left table, matches or NULL

SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

Result: Alice/50, Alice/75, Bob/20, Carol/NULL — every customer appears at least once, even with no orders.

RIGHT JOIN — all of the right table, matches or NULL

SELECT c.name, o.total
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;

Functionally identical to swapping the tables and using LEFT JOIN. Most style guides prefer always writing LEFT JOIN and reordering the FROM/JOIN tables instead, since it reads more consistently across a codebase.

FULL OUTER JOIN — everything from both sides

SELECT c.name, o.total
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;

Result: every customer (even unmatched, like Carol) and every order (even one belonging to a since-deleted customer, if such a row existed) — NULL fills in whichever side lacks a match. MySQL has no native FULL OUTER JOIN; it's emulated with LEFT JOIN UNION RIGHT JOIN.

Picking the right one

• Need only rows that exist in both → INNER JOIN.
• Need every row from a "primary" table regardless of whether related data exists → LEFT JOIN (e.g., all customers, with order totals if any).
• Need a full reconciliation between two sets, including orphans on either side → FULL OUTER JOIN (common in data-quality/migration checks).