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;

id	name
1	J.R.R. Tolkien
2	Isaac Asimov
3	Agatha Christie

books

Loaded Database:joins.database-init

SELECT * FROM books ORDER BY id;

id	title	author_id
1	The Fellowship of the Ring	1
2	The Two Towers	1
3	I, Robot	2
4	Beowulf

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;

name	title
Isaac Asimov	I, Robot
J.R.R. Tolkien	The Fellowship of the Ring
J.R.R. Tolkien	The Two Towers

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;

name	title
Agatha Christie
Isaac Asimov	I, Robot
J.R.R. Tolkien	The Fellowship of the Ring
J.R.R. Tolkien	The Two Towers

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;

name	title
Isaac Asimov	I, Robot
J.R.R. Tolkien	The Fellowship of the Ring
J.R.R. Tolkien	The Two Towers
	Beowulf

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;

name	title
Agatha Christie
Isaac Asimov	I, Robot
J.R.R. Tolkien	The Fellowship of the Ring
J.R.R. Tolkien	The Two Towers
	Beowulf

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;

name	title
Agatha Christie	Beowulf
Agatha Christie	I, Robot
Agatha Christie	The Fellowship of the Ring
Agatha Christie	The Two Towers
Isaac Asimov	Beowulf
Isaac Asimov	I, Robot
Isaac Asimov	The Fellowship of the Ring
Isaac Asimov	The Two Towers
J.R.R. Tolkien	Beowulf
J.R.R. Tolkien	I, Robot
J.R.R. Tolkien	The Fellowship of the Ring
J.R.R. Tolkien	The Two Towers

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.