Joins — Combining Tables

JOINs combine rows from two or more tables based on a related column. They are the single most important feature of relational databases — and the source of most beginner confusion.

The four kinds

• INNER JOIN — only rows where the join condition matches in both tables.
• LEFT JOIN — every row from the left table; matched rows from the right (NULL when no match).
• RIGHT JOIN — mirror of LEFT. Less common in practice.
• FULL OUTER JOIN — every row from both tables, NULLs where there's no match.

Example schema

users  (id, name)
posts  (id, user_id, title, created_at)

INNER JOIN — every post WITH its author

SELECT users.name, posts.title
FROM posts
INNER JOIN users ON users.id = posts.user_id;

LEFT JOIN — every user, with their posts (or NULL if none)

SELECT users.name, posts.title
FROM users
LEFT JOIN posts ON posts.user_id = users.id;

Find users WITHOUT any posts

SELECT users.name
FROM users
LEFT JOIN posts ON posts.user_id = users.id
WHERE posts.id IS NULL;