SQL, Refreshed
Joins
Lesson 3 of 5
What you'll learn
- Relate rows across tables with
INNER JOIN - Contrast
INNER JOINwithLEFT JOIN - See how foreign keys make joins meaningful
Relational data is split across tables to avoid repetition. A users table holds people; a posts table holds posts, each pointing back to its author with a foreign key column like user_id. A join recombines them on demand.
SELECT users.name, posts.title
FROM users
INNER JOIN posts ON posts.user_id = users.id;
For every post, Postgres finds the user whose id matches posts.user_id and emits a combined row. A user with three posts appears three times; a user with none doesn't appear at all.
INNER drops unmatched rows; LEFT keeps them
An INNER JOIN returns only pairs that match on both sides. A LEFT JOIN keeps every row from the left table, filling the right side with NULL when there's no match — perfect for "users and their posts, including users who haven't posted."
SELECT users.name, posts.title
FROM users
LEFT JOIN posts ON posts.user_id = users.id;
-- Users with no posts still appear, with title = NULL.
Mind the match column
Joining on the wrong columns silently produces wrong rows, not an error. Always join a foreign key to the primary key it references — here posts.user_id to users.id.
An inner join is just a nested loop: for each post, find the matching user, pair them up. The challenge models exactly that.
Run it. Pair each post with its author by matching post.user_id to user.id.
Next: why some queries are fast and others crawl.
Sign in to save your progress across devices.