INNER JOIN vs LEFT JOIN vs RIGHT JOIN (With Visuals)

The sample tables

Every example below uses these two small tables. Notice two deliberate edge cases: Carol has no orders, and order 104 belongs to customer 4, who doesn't exist in customers.

INNER JOIN — matches only

An INNER JOIN returns a row only when the join key exists in both tables. Unmatched rows on either side are dropped.

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

Carol (no orders) and order 104 (no customer) are both dropped — they have no match on the other side.

LEFT JOIN — all of the left table

A LEFT JOIN (a.k.a. LEFT OUTER JOIN) keeps every row from the left table (customers) and attaches matching orders. Where there's no match, the order columns come back as NULL.

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

Carol now appears with NULLs. Order 104 is still gone — it's on the right side, which LEFT JOIN doesn't force-keep.

RIGHT JOIN — all of the right table

A RIGHT JOIN is the mirror: it keeps every row from the right table (orders) and attaches matching customers, with NULL where there's no match.

SELECT c.name, o.order_id, o.amount
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

Order 104 (the orphan) now appears with a NULL customer. Carol is gone — she's on the left side this time.

LEFT vs RIGHT (and why RIGHT is rare)

LEFT JOIN and RIGHT JOIN are the same operation in opposite directions. Any right join can be rewritten as a left join by swapping the table order — and most teams prefer to, because reading left-to-right matches how we read the query:

-- These two return the same rows:
SELECT * FROM customers c RIGHT JOIN orders o ON c.customer_id = o.customer_id;
SELECT * FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id;

Which JOIN should you use?

The LEFT-JOIN-that-acts-like-INNER gotcha

This is the most common join bug. You write a LEFT JOIN, but a filter on the right table in WHERE silently removes the NULL rows — turning it back into an INNER JOIN:

-- ❌ Carol disappears: WHERE filters her NULL amount away
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.amount > 100;

-- ✅ Put the condition in ON to keep unmatched left rows
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id AND o.amount > 100;

Rule: conditions that should filter the final result go in WHERE; conditions that define what counts as a match go in ON. For more on why, see SQL execution order.