PostgreSQL Joins - PostgreSQL Tutorial

Joins combine rows from two tables using a related column.

SQL

CREATE TABLE authors (
    id   SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE books (
    id        SERIAL PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INT REFERENCES authors(id)
);

INNER JOIN

Only rows that match on both sides:

SQL

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

A book with author_id NULL, or pointing at a missing author, is excluded.

LEFT JOIN

Every row from the left table; NULLs where the right has no match:

SQL

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

This is the one to use when you want "all books, with their author if known".

RIGHT JOIN and FULL JOIN

SQL

-- all authors, even those with no books
SELECT a.name, b.title
FROM books b
RIGHT JOIN authors a ON a.id = b.author_id;

-- everything from both sides
SELECT a.name, b.title
FROM books b
FULL JOIN authors a ON a.id = b.author_id;

RIGHT JOIN is rare in practice — most people flip the table order and use LEFT JOIN, which is easier to read.

Finding rows with no match

A very common pattern — authors who have written nothing:

SQL

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

CROSS JOIN

Every combination of both tables. Occasionally useful (generating schedules), usually an accident:

SQL

SELECT a.name, c.name FROM authors a CROSS JOIN categories c;

If you forget the ON clause on a normal join you get this, which is why a query suddenly returning millions of rows is usually a missing join condition.

Joining several tables

SQL

SELECT b.title, a.name AS author, c.name AS category
FROM books b
JOIN authors a    ON a.id = b.author_id
JOIN categories c ON c.id = b.category_id
WHERE b.price < 600
ORDER BY b.title;

WHERE vs ON in an outer join

This matters and trips people up. A condition in WHERE filters after the join, turning a LEFT JOIN back into an INNER JOIN:

SQL

-- unintentionally drops books with no author
SELECT b.title, a.name FROM books b
LEFT JOIN authors a ON a.id = b.author_id
WHERE a.country = 'Nepal';

-- keeps all books; only the join is restricted
SELECT b.title, a.name FROM books b
LEFT JOIN authors a ON a.id = b.author_id AND a.country = 'Nepal';

Always alias your tables

b, a, c above keep long queries readable and make ambiguous column names impossible.