SQL JOINs explained: INNER, LEFT, RIGHT and FULL with clear examples

The JOIN is the operation turning a pile of tables into a relational database. Yet the difference between INNER, LEFT, RIGHT and FULL still generates interview panic and production bugs. With two small tables and visible results, the mystery ends forever.

The example tables

Two minimal tables: authors and books. Note the asymmetries — they're the key to everything:

authors

books

Carmen has no books. "Trace" has no author (NULL — an orphan book). Each join exists to answer a different question about these gaps.

INNER JOIN: only matches

SELECT a.name, b.title
FROM authors a
INNER JOIN books b ON b.author_id = a.id;

Only rows matching on both sides. Carmen disappears (no books), "Trace" disappears (no author). It's everyone's mental default — and most-used because most questions are "give me the matches". Careful: Ana appears TWICE because she has two books; join output is combined rows, not grouped entities.

LEFT JOIN: keep all of the left

SELECT a.name, b.title
FROM authors a
LEFT JOIN books b ON b.author_id = a.id;

The entire left table survives, filled with NULL where no match. The question it answers: "all authors, plus their book if any".

Its star application — finding those with NOTHING:

SELECT a.name
FROM authors a
LEFT JOIN books b ON b.author_id = a.id
WHERE b.id IS NULL;

-- Result: Carmen

This pattern ("left join + where null") is THE idiomatic anti-join: elements in A without a counterpart in B. Authors without books, customers without orders, users without recent logins.

RIGHT and FULL: completing the picture

RIGHT JOIN is LEFT inverted: keeps the whole right table.

SELECT a.name, b.title
FROM authors a
RIGHT JOIN books b ON b.author_id = a.id;

In practice almost nobody writes RIGHT JOIN — swap table order and use LEFT, which reads more naturally. It exists for standard symmetry.

FULL OUTER JOIN keeps both sides: Carmen AND "Trace" appear, each with NULLs in its gap. For "show me everything from both worlds with matches where they exist" — inventory vs movements, users vs sessions.

CROSS JOIN: the cartesian product

No ON condition: each row × each row. 3 authors × 4 books = 12 rows. Seems useless until you need every combination: all dates × all products for a monthly report, or all pairs for cross comparisons. Use deliberately; accidentally (malformed or forgotten ON) it generates million-row explosions that take servers down.

The classic mistakes

Filtering the left table in WHERE kills the LEFT JOIN:

-- Wants: all authors with their books published in 2026 (if any)
-- WRONG:
SELECT a.name, b.title
FROM authors a LEFT JOIN books b ON b.author_id = a.id
WHERE b.year = 2026;          -- Carmen disappears again!

-- RIGHT: move condition into ON
SELECT a.name, b.title
FROM authors a LEFT JOIN books b
  ON b.author_id = a.id AND b.year = 2026;   -- Carmen returns with NULL

Condition in WHERE = filter AFTER the join (NULLs get dropped). Condition in ON = defines the pairing (matchless rows survive). THE distinction of LEFT JOIN.

Counting over a JOIN duplicates rows: COUNT(*) over authors-books counts books, not authors. To count authors with books: COUNT(DISTINCT a.id). The silentest dashboard bug around.

NULL never equals: if author_id could be 0 or another sentinel instead of NULL, joins change behavior. Normalize nulls before reasoning about results.

Formatting big queries

A triple join with subqueries unformatted is unreadable — and unreadable hides bugs. Paste your query into our SQL formatter to see it structured before modifying it, and if it's slow, the natural next step is checking indexes and execution plans.

FAQ

Are INNER JOIN and JOIN the same? Yes, bare JOIN implies INNER. Writing it fully helps read intent.

Can I chain multiple JOINs? Yes, and it's normal: orders → customers → countries → regions. Each ON joins against the accumulated result so far; with chained LEFTs, order matters enormously.

USING and NATURAL JOIN? USING (id) is syntactic sugar when columns share a name (and collapses the duplicate column). NATURAL JOIN joins ALL same-named columns automatically — convenient until dangerous: adding a same-named column silently breaks queries. Avoid it.

Format your complex-join queries using our SQL Formatter, free and right in your browser.