JOINs

After this lesson, you will be able to: Use INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN and reason about them visually.

JOINs are how relational databases earn their name. The biggest divider between 'wrote a few queries' and 'productive SQL engineer.'

Prerequisites:Filtering and Sorting

INNER JOIN — the default

Returns rows where BOTH tables have a match.

sql

-- Users with their posts
SELECT u.id, u.name, p.title
FROM users u
INNER JOIN posts p ON p.user_id = u.id;

-- 'INNER' is optional; just `JOIN` means INNER JOIN
SELECT u.id, u.name, p.title
FROM users u
JOIN posts p ON p.user_id = u.id;

-- Users with no posts are excluded (no matching post row)

LEFT JOIN — keep all from left

Returns all rows from the left table, NULL for right when no match.

sql

-- All users, including those with no posts
SELECT u.id, u.name, p.title
FROM users u
LEFT JOIN posts p ON p.user_id = u.id;
-- Users with no posts have NULL in title column

-- Common pattern: 'find left rows with NO match on right'
SELECT u.id, u.name
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
WHERE p.id IS NULL;
-- Users who have never posted

FULL OUTER JOIN — keep all from both

Less common; useful for reconciling two lists.

sql

-- Compare two product lists (e.g., new catalog vs old)
SELECT n.sku, n.name AS new_name, o.name AS old_name
FROM new_catalog n
FULL OUTER JOIN old_catalog o ON n.sku = o.sku
WHERE n.sku IS NULL OR o.sku IS NULL;
-- Returns: products only in new, or only in old

Joining three+ tables

Chain JOINs as needed.

sql

-- Users + posts + comments
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INT REFERENCES posts(id),
  author_id INT REFERENCES users(id),
  body TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

SELECT u.name AS post_author, p.title, c.body AS comment, cu.name AS commenter
FROM users u
JOIN posts p ON p.user_id = u.id
LEFT JOIN comments c ON c.post_id = p.id
LEFT JOIN users cu ON cu.id = c.author_id
WHERE p.published = TRUE;

Common mistakes

INNER JOIN when you needed LEFT JOIN → quietly drops rows. Putting LEFT JOIN's right-side condition in WHERE (cancels the LEFT-ness, becomes INNER). Cartesian product from missing ON clause (10M rows from 1k × 10k). Ambiguous column names without table alias (`SELECT id FROM users JOIN posts`, which id?).

←Filtering and Sorting

Back to SQL

GROUP BY and Aggregations→