Joins & Relationships

Why Joins Exist

In a well-normalized database, related information lives in separate tables. Joins let you combine rows from multiple tables in a single query based on a matching condition — usually a foreign key relationship. Understanding join types is essential because the wrong join silently returns too many or too few rows.

Join Types

INNER JOIN: Matching Rows Only

-- Fetch published posts with their author's name
SELECT u.name    AS author,
       p.title,
       p.published_at
FROM   posts p
JOIN   users u ON u.id = p.user_id   -- JOIN = INNER JOIN
WHERE  p.published = true
ORDER  BY p.published_at DESC
LIMIT  20;

LEFT JOIN: Include Unmatched Rows

-- All users, even those with no posts yet
SELECT u.name,
       COUNT(p.id) AS post_count
FROM   users u
LEFT   JOIN posts p ON p.user_id = u.id
                   AND p.published = true
GROUP  BY u.id, u.name
ORDER  BY post_count DESC;

Note: filter conditions on the joined table belong in the ON clause, not a WHERE clause, or the LEFT JOIN behaves like an INNER JOIN.

Multi-Table Joins

-- Posts with author name and all their tags
SELECT p.title,
       u.name         AS author,
       STRING_AGG(t.name, ', ' ORDER BY t.name) AS tags
FROM   posts p
JOIN   users     u  ON u.id  = p.user_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.name
ORDER  BY p.created_at DESC;

CROSS JOIN and SELF JOIN

-- CROSS JOIN: every combination of rows (Cartesian product)
SELECT a.name, b.name
FROM employees a
CROSS JOIN employees b;

-- SELF JOIN: join a table to itself (e.g., org chart)
SELECT e.name AS employee,
       m.name AS manager
FROM   employees e
LEFT   JOIN employees m ON m.id = e.manager_id;