Types of JOINs

As we saw in Introduction to JOIN, a JOIN combines rows from two tables using a matching column. There we used JOIN, which is short for INNER JOIN — the default, but not the only option.

Types of JOIN

Keyword	What it keeps
INNER JOIN	Only rows that match on both sides.
LEFT JOIN	Every row from the left table, matched or not.
RIGHT JOIN	Every row from the right table, matched or not.
FULL OUTER JOIN	Every row from both tables, matched or not.
CROSS JOIN	Every combination of rows from both tables — no ON, no matching.

A real scenario

Some authors have no book yet, and one book has no listed author. Watch what each JOIN type does with those gaps.

authors

Loaded Database:joins.database-init

SELECT * FROM authors ORDER BY id;

books

Loaded Database:joins.database-init

SELECT * FROM books ORDER BY id;

INNER JOIN

Agatha Christie and Beowulf both disappear: neither has a match on the other side.

Loaded Database:joins.database-init

SELECT authors.name, books.title
FROM authors
JOIN books ON books.author_id = authors.id
ORDER BY authors.name, books.title;

LEFT JOIN

Agatha Christie survives with a NULL title. Beowulf is still gone — it isn't on the left.

Loaded Database:joins.database-init

SELECT authors.name, books.title
FROM authors
LEFT JOIN books ON books.author_id = authors.id
ORDER BY authors.name NULLS LAST, books.title NULLS LAST;

RIGHT JOIN

Same tables, same column order — only the join keyword changed. Beowulf survives with a NULL name; Agatha Christie is gone.

Loaded Database:joins.database-init

SELECT authors.name, books.title
FROM authors
RIGHT JOIN books ON books.author_id = authors.id
ORDER BY authors.name NULLS LAST, books.title NULLS LAST;

FULL OUTER JOIN

Nobody is left out. Agatha Christie and Beowulf both survive, each with a NULL on the missing side.

Loaded Database:joins.database-init

SELECT authors.name, books.title
FROM authors
FULL OUTER JOIN books ON books.author_id = authors.id
ORDER BY authors.name NULLS LAST, books.title NULLS LAST;

CROSS JOIN

No ON clause, so nothing is matched or excluded: every author pairs with every book. 3 authors × 4 books = 12 rows.

Loaded Database:joins.database-init

SELECT authors.name, books.title
FROM authors
CROSS JOIN books
ORDER BY authors.name, books.title;

What We Learned

INNER JOINkeeps only rows where both sides match.
LEFT / RIGHT JOINkeep every row from one named side, filling the other with NULL.
FULL OUTER JOINkeeps every row from both sides, filling gaps with NULL.
CROSS JOINpairs every row from one table with every row from the other.
NULLS LASTan ORDER BY option that sorts NULL values to the end instead of the default first.