What is the difference between INNER JOIN, LEFT OUTER JOIN, and FULL OUTER JOIN?
The short answer
INNER JOIN returns only rows where the join condition matches in both tables. LEFT OUTER JOIN returns every row from the left table plus matching rows from the right, filling NULLs where there is no match. FULL OUTER JOIN returns all rows from both sides, with NULLs wherever one side has no match.
How to think about it
The question sounds basic, but interviewers care whether you can reason about row counts and NULLs — not just recite definitions. The best answer starts with a concrete use case: “I’d reach for LEFT JOIN when I want all customers whether or not they’ve ordered.”
Row behaviour at a glance
| Join type | Rows kept from A | Rows kept from B |
|---|---|---|
| INNER | matched only | matched only |
| LEFT OUTER | all | matched only |
| RIGHT OUTER | matched only | all |
| FULL OUTER | all | all |
Code example
-- orders may have a customer_id; customers may have no orders yet
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;
-- customers with no orders appear with NULL in order_id, amount-- FULL OUTER JOIN: all customers and all orders, whether matched or not
SELECT c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;See it in action
Run the query below. Notice that customer “Dana” (no orders) disappears with INNER but stays with LEFT. Change INNER JOIN to LEFT JOIN and watch the difference.
What to mention in an interview
- FULL OUTER JOIN is not supported in MySQL; use
LEFT JOIN UNION ALL RIGHT JOIN WHERE left.id IS NULL. - RIGHT JOIN is just a LEFT JOIN with tables swapped — most style guides prefer LEFT for readability.
- Lead with the use case: “I’d reach for LEFT JOIN when I want all customers, whether they’ve ordered or not.”
- Always think about what happens to unmatched rows — that reasoning separates strong candidates.