What is the difference between INNER JOIN, LEFT OUTER JOIN, and FULL OUTER JOIN?

How to think about it

The question sounds basic, but the interviewer is really watching whether you reason about row counts and NULLs — not whether you can recite three definitions. The strongest answers lead with a use case: “I’d reach for LEFT JOIN when I want all customers, whether or not they’ve ordered.”

Row behaviour at a glance

The whole family differs only in which unmatched rows survive:

Join type	Rows kept from A (left)	Rows kept from B (right)
INNER	matched only	matched only
LEFT OUTER	all	matched only
RIGHT OUTER	matched only	all
FULL OUTER	all	all

A worked example — INNER vs LEFT

Four customers, but Dana has placed no orders. INNER JOIN keeps only matched pairs, so Dana vanishes:

SELECT c.name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
ORDER BY c.name;

name	order_id	amount
Aarav	101	120
Aarav	102	80
Bea	103	200
Chen	104	50

Switch one word — INNER to LEFT — and Dana returns, padded with NULL where the right side has nothing to offer:

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
ORDER BY c.name;

name	order_id	amount
Aarav	101	120
Aarav	102	80
Bea	103	200
Chen	104	50
Dana	NULL	NULL

That extra Dana row — present in one result, absent in the other — is the difference between the join types. Everything else follows from “what happens to the unmatched rows.”

In an interview, mention

• FULL OUTER JOIN isn’t supported in MySQL — emulate it with LEFT JOIN ... UNION ALL ... RIGHT JOIN ... WHERE left.id IS NULL.
• RIGHT JOIN is just a LEFT JOIN with the tables swapped; most style guides prefer LEFT for readability.
• Always reason aloud about what happens to unmatched rows — that habit is what separates strong candidates.