Lesson 08 intermediate

Joins and Relationships

Model one-to-many and many-to-many relationships, add tags to Orbit with a join table, and combine tables with inner, left, full and cross joins; find rows without matches using anti-joins, filter with IN and EXISTS subqueries, get the latest row per group with LATERAL, and stack results with UNION.

Real data is spread over several tables, and joins put it back together. In this lesson you add tags to Orbit, which introduces the most important relationship pattern in SQL, and practise every kind of join on it.

Relationships

RelationshipExampleHow it is stored
One-to-manyA user has many notesnotes.user_id references users.id
Many-to-manyA note has many tags, a tag is on many notesA join table with two foreign keys
One-to-oneA user has one profileA foreign key that is also unique

You already have a one-to-many relationship. Tags need a many-to-many one:

CREATE TABLE tags (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL UNIQUE
);

CREATE TABLE note_tags (
  note_id bigint NOT NULL REFERENCES notes (id) ON DELETE CASCADE,
  tag_id  bigint NOT NULL REFERENCES tags (id) ON DELETE CASCADE,
  PRIMARY KEY (note_id, tag_id)
);

INSERT INTO tags (name) VALUES ('work'), ('personal'), ('postgres');
INSERT INTO note_tags (note_id, tag_id) VALUES (1, 2), (2, 1), (3, 2), (4, 1), (4, 3), (5, 1);

The composite primary key (note_id, tag_id) stops the same tag being attached twice. The resulting data:

NoteTags
1 Shopping listpersonal
2 Deploy checklistwork
3 Book ideaspersonal
4 Postgres tipswork, postgres
5 Meeting noteswork

Inner join

An inner join returns pairs of rows that match the join condition. Each note with its author:

SELECT n.id, n.title, u.name AS author
FROM notes n
JOIN users u ON u.id = n.user_id
ORDER BY n.id;
 id |      title       |   author
----+------------------+------------
  1 | Shopping list    | Ana Silva
  2 | Deploy checklist | Ana Silva
  3 | Book ideas       | Ben Carter
  4 | Postgres tips    | Ben Carter
  5 | Meeting notes    | Ana Silva
(5 rows)

n and u are table aliases. Use them in every query with more than one table, so it is always clear which table a column comes from. JOIN is short for INNER JOIN.

When both columns have the same name, USING is shorter: JOIN note_tags USING (note_id).

Left join

Chen has no notes, so the inner join above never mentions Chen. A left join keeps every row from the left table and fills the right side with NULLs when nothing matches:

SELECT u.name, count(n.id) AS notes
FROM users u
LEFT JOIN notes n ON n.user_id = u.id
GROUP BY u.id, u.name
ORDER BY u.id;
    name    | notes
------------+-------
 Ana Silva  |     3
 Ben Carter |     2
 Chen Wei   |     0
(3 rows)

Note count(n.id) rather than count(*): for Chen the joined row exists but n.id is NULL, and count(column) skips NULLs, giving 0 instead of 1.

Warning: A condition on the right-hand table belongs in the ON clause of a left join. LEFT JOIN notes n ON n.user_id = u.id WHERE n.pinned silently turns it back into an inner join, because Chen’s row has n.pinned NULL and is filtered out. Write LEFT JOIN notes n ON n.user_id = u.id AND n.pinned instead.

RIGHT JOIN is the mirror image and rarely needed. FULL JOIN keeps unmatched rows from both sides, which is useful for comparing two lists.

Anti-join: rows without a match

“Users who never wrote a note” is a classic question. Use NOT EXISTS:

SELECT u.name
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM notes n WHERE n.user_id = u.id);
   name
----------
 Chen Wei
(1 row)

The same result is possible with LEFT JOIN ... WHERE n.id IS NULL. Avoid NOT IN (SELECT user_id FROM notes): if the subquery ever returns a NULL, NOT IN returns no rows at all.

Many-to-many: joining through the join table

Every note with its tags, collapsed into one line with string_agg:

SELECT n.id, n.title, string_agg(t.name, ', ' ORDER BY t.name) AS tags
FROM notes n
JOIN note_tags nt ON nt.note_id = n.id
JOIN tags t ON t.id = nt.tag_id
GROUP BY n.id, n.title
ORDER BY n.id;
 id |      title       |      tags
----+------------------+----------------
  1 | Shopping list    | personal
  2 | Deploy checklist | work
  3 | Book ideas       | personal
  4 | Postgres tips    | postgres, work
  5 | Meeting notes    | work
(5 rows)

And the other direction, how often each tag is used, including tags that are not used at all:

SELECT t.name, count(nt.note_id) AS uses
FROM tags t
LEFT JOIN note_tags nt ON nt.tag_id = t.id
GROUP BY t.id, t.name
ORDER BY uses DESC, t.name;
   name   | uses
----------+------
 work     |    3
 personal |    2
 postgres |    1
(3 rows)

Subqueries with IN and EXISTS

A subquery is a query inside another query. Users who have pinned at least one note:

SELECT name FROM users
WHERE id IN (SELECT user_id FROM notes WHERE pinned)
ORDER BY name;
    name
------------
 Ana Silva
 Ben Carter
(2 rows)

EXISTS (SELECT 1 FROM notes WHERE notes.user_id = users.id AND pinned) returns the same rows. The planner usually turns both into the same efficient semi-join, so choose whichever reads better.

A subquery that returns one value can be used like a column:

SELECT title, (SELECT name FROM users u WHERE u.id = n.user_id) AS author
FROM notes n WHERE id = 4;

LATERAL: the latest row per group

“Each user’s most recent note” needs a subquery that runs once per user, with a LIMIT. That is what LATERAL allows: the subquery may refer to columns of tables listed before it.

SELECT u.name, latest.title, latest.created_at::date AS day
FROM users u
CROSS JOIN LATERAL (
  SELECT title, created_at
  FROM notes n
  WHERE n.user_id = u.id
  ORDER BY created_at DESC
  LIMIT 1
) AS latest
ORDER BY u.id;
    name    |     title     |    day
------------+---------------+------------
 Ana Silva  | Meeting notes | 2026-11-02
 Ben Carter | Postgres tips | 2026-10-12
(2 rows)

Change CROSS JOIN LATERAL (...) AS latest to LEFT JOIN LATERAL (...) AS latest ON true to keep Chen with empty columns. Change LIMIT 1 to LIMIT 3 and you have “top three per group”.

Cross joins and UNION

A cross join pairs every row of one table with every row of the other. It is rarely what you want by accident, but useful on purpose, for example to build a grid of every user and every tag for a report.

UNION stacks the results of two queries with the same columns. It removes duplicates; UNION ALL keeps them and is faster:

SELECT 'user' AS kind, name AS label FROM users
UNION ALL
SELECT 'tag', name FROM tags
ORDER BY kind, label;

INTERSECT and EXCEPT return rows found in both queries, or in the first but not the second.

How joins perform

Postgres chooses between three join methods: a nested loop (great when one side is small and the other has an index on the join column), a hash join (good for large unsorted inputs) and a merge join (good when both sides are sorted). You do not choose; the planner does, based on statistics. Your job is to give it indexes on the columns you join on, especially foreign keys. Postgres creates indexes for primary keys and unique constraints automatically, but not for foreign key columns like notes.user_id. Lesson 11 adds them.

Quick reference

TaskSQL
Matching rowsFROM a JOIN b ON b.a_id = a.id
Keep all of the left sideLEFT JOIN ... ON ...
Rows without a matchWHERE NOT EXISTS (SELECT 1 FROM b WHERE ...)
Has at least one matchWHERE EXISTS (...) or IN (SELECT ...)
Many-to-manyJoin through the join table
Latest per groupCROSS JOIN LATERAL (... ORDER BY ... LIMIT 1)
Values into one stringstring_agg(col, ', ' ORDER BY col)
Stack resultsUNION ALL (or UNION to remove duplicates)

Exercise

A report uses LEFT JOIN notes n ON n.user_id = u.id WHERE n.pinned, and users without notes disappear. Why?

  1. LEFT JOIN does not keep unmatched rows when grouping
  2. The foreign key prevents NULLs
  3. For unmatched users n.pinned is NULL, so the WHERE clause removes them
  4. Booleans cannot be used in WHERE after a join
Show answer

Answer: C. The WHERE clause runs after the join and drops rows where the condition is not true. Move the condition into the ON clause to filter only the notes while keeping every user.

Exercise

Which query safely finds users with no notes, even if notes.user_id could contain NULLs?

  1. WHERE id NOT IN (SELECT user_id FROM notes)
  2. WHERE NOT EXISTS (SELECT 1 FROM notes n WHERE n.user_id = users.id)
  3. WHERE id <> ALL (SELECT user_id FROM notes)
  4. JOIN notes ON notes.user_id <> users.id
Show answer

Answer: B. NOT IN and <> ALL return no rows when the subquery contains a NULL, because the comparison becomes unknown. NOT EXISTS is not affected.

Exercise

Why does the many-to-many table note_tags use PRIMARY KEY (note_id, tag_id)?

  1. Join tables are not allowed to have identity columns
  2. It makes the table use less disk space than any other key
  3. It sorts notes by tag automatically
  4. It prevents attaching the same tag to the same note twice and indexes lookups by note
Show answer

Answer: D. A composite key makes each pair unique. Its index also serves queries that filter on note_id; add a separate index on tag_id for lookups in the other direction.

Key takeaways

  • One-to-many uses a foreign key column; many-to-many uses a join table with two foreign keys.
  • Inner joins return matches only; left joins keep every left row and put conditions for the right side in ON.
  • Use NOT EXISTS for anti-joins, never NOT IN with a subquery that might return NULL.
  • LATERAL subqueries solve “latest” and “top N per group”.
  • Index your foreign key columns: Postgres does not do it for you.

FAQ

Is a JOIN slower than storing everything in one table?

Not with proper indexes. Joins on indexed keys are one of the things relational databases are best at. Duplicating data to avoid joins usually causes bugs when the copies drift apart.

What is the difference between JOIN … ON and JOIN … USING?

USING (col) is shorthand for joining on equal columns with the same name, and the column appears only once in SELECT *. ON accepts any condition, including different column names and extra filters.

Can I join tables from two different databases?

Not directly: a connection belongs to one database. Use the postgres_fdw extension to make tables of another database or server appear as local foreign tables, or keep related data in schemas of the same database.

Should tags be stored as a text array instead of a join table?

An array (tags text[]) with a GIN index is simpler and works well when tags are free-form labels. A join table is better when tags have their own data (colour, owner), must be renamed in one place, or need foreign-key integrity.

How do I delete rows based on another table?

Use DELETE ... USING, for example DELETE FROM note_tags nt USING tags t WHERE t.id = nt.tag_id AND t.name = 'old';. UPDATE ... FROM works the same way for updates.

↑ ↓ navigate Enter open Esc close