JOINs and Relations
INNER JOIN, LEFT JOIN, self-joins, and joining multiple tables
Skills taught: pg.inner-join, pg.left-join, pg.self-join, pg.multi-table-joins
INNER JOIN — only matching rows
-- Tables:
-- users (id, email, username)
-- posts (id, user_id, title, published)
-- Get all published posts with author info
SELECT
posts.id,
posts.title,
users.username AS author,
users.email AS author_email
FROM posts
INNER JOIN users ON posts.user_id = users.id
WHERE posts.published = TRUE
ORDER BY posts.created_at DESC;
-- Using table aliases (cleaner syntax)
SELECT
p.id,
p.title,
u.username AS author
FROM posts p
JOIN users u ON p.user_id = u.id -- INNER JOIN = JOIN
WHERE p.published = TRUE;
LEFT JOIN — all rows from the left table
-- Show all users even if they have no posts
SELECT
u.id,
u.username,
COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.username
ORDER BY post_count DESC;
-- Result:
-- alice | 12
-- bob | 3
-- carol | 0 ← included because LEFT JOIN, post_count is COUNT(p.id) = 0 for no matches
-- Find users who have NEVER posted (anti-join pattern)
SELECT u.id, u.username
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE p.id IS NULL; -- no matching post exists
Joining multiple tables
-- Tables: users, posts, comments, tags, post_tags
-- Get posts with author, comment count, and tags
SELECT
p.id,
p.title,
u.username AS author,
COUNT(DISTINCT c.id) AS comment_count,
ARRAY_AGG(DISTINCT t.name ORDER BY t.name) AS tags
FROM posts p
JOIN users u ON u.id = p.user_id
LEFT JOIN comments c ON c.post_id = p.id
LEFT JOIN post_tags pt ON pt.post_id = p.id
LEFT JOIN tags t ON t.id = pt.tag_id
WHERE p.published = TRUE
GROUP BY p.id, p.title, u.username
ORDER BY p.created_at DESC
LIMIT 20;
Self-join — joining a table to itself
-- employees: id, name, manager_id (references employees.id)
SELECT
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY m.name NULLS LAST, e.name;
Frequently Asked Questions
When should I use a subquery vs a JOIN?
JOINs are usually faster and the query planner optimizes them well. Use subqueries when: you need to filter on an aggregate result (EXISTS/NOT EXISTS), the subquery is used in a scalar context (returning one value), or the logic is clearer as a subquery. Correlated subqueries (referencing the outer query) run once per row and can be very slow — often rewritable as a JOIN.