SQL JOIN Types Explained: INNER, LEFT, RIGHT, and FULL

Understand the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN in SQL, with examples of what rows each one returns.

Published September 18, 2026

A JOIN combines rows from two tables on a matching column. INNER returns only matches, LEFT keeps every left row, RIGHT keeps every right row and FULL keeps everything from both.

The concept

Think of two overlapping circles. INNER JOIN is the overlap. LEFT JOIN is the whole left circle, with NULLs where the right side has no match. RIGHT is the mirror image. FULL OUTER is both circles together.

Rows without a match are filled with NULL on the missing side, which is how you find 'orphans' with WHERE right.id IS NULL.

Same tables, different joins

INNER JOIN → Ada, Ada, Bob
LEFT JOIN → Ada, Ada, Bob, Cy (NULL order)
customers with no orders → LEFT JOIN … WHERE o.id IS NULL → Cy
customers: Ada, Bob, Cy · orders: Ada(2), Bob(1)

Practical examples

INNER JOIN

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

LEFT JOIN with an anti-join

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

FULL OUTER JOIN (PostgreSQL)

MySQL has no FULL JOIN; emulate it with UNION of a LEFT and RIGHT join.

SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id;

Common mistakes

  • Forgetting the ON clause and producing a cartesian product.
  • Putting a filter on the right table in WHERE, which turns a LEFT JOIN into an INNER JOIN — put it in ON.
  • Joining on non-unique columns and multiplying rows.

Key takeaways

  • INNER = matches only; LEFT/RIGHT = keep one side; FULL = keep both.
  • Unmatched sides become NULL.
  • Index the join columns.

FAQ

LEFT JOIN vs LEFT OUTER JOIN?

They are the same; OUTER is optional.

Why did my row count explode?

The join key is not unique on one side, so each row matches many.

More SQL articles