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
| Relationship | Example | How it is stored |
|---|---|---|
| One-to-many | A user has many notes | notes.user_id references users.id |
| Many-to-many | A note has many tags, a tag is on many notes | A join table with two foreign keys |
| One-to-one | A user has one profile | A 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:
| Note | Tags |
|---|---|
| 1 Shopping list | personal |
| 2 Deploy checklist | work |
| 3 Book ideas | personal |
| 4 Postgres tips | work, postgres |
| 5 Meeting notes | work |
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
| Task | SQL |
|---|---|
| Matching rows | FROM a JOIN b ON b.a_id = a.id |
| Keep all of the left side | LEFT JOIN ... ON ... |
| Rows without a match | WHERE NOT EXISTS (SELECT 1 FROM b WHERE ...) |
| Has at least one match | WHERE EXISTS (...) or IN (SELECT ...) |
| Many-to-many | Join through the join table |
| Latest per group | CROSS JOIN LATERAL (... ORDER BY ... LIMIT 1) |
| Values into one string | string_agg(col, ', ' ORDER BY col) |
| Stack results | UNION 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?
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?
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)?
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 EXISTSfor anti-joins, neverNOT INwith a subquery that might return NULL. LATERALsubqueries 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.