A Gentle Introduction to JOIN

Picture a posts table for a blog. Each post needs an author, but it would be wasteful to retype that author's full username into every single post row. Instead, each post simply stores the author's id, in a column often called author_id, pointing back at a row in the users table.

-- posts table
id | title          | author_id
1  | Hello World     | 2
2  | My First Recipe | 3

Here, post 1 was written by whoever has id 2 in the users table, and post 2 was written by whoever has id 3. To actually see the author's username alongside each post title, you need to connect these two tables together. That is exactly what JOIN does.

SELECT posts.title, users.username
FROM posts
JOIN users ON posts.author_id = users.id;

Read the ON part carefully: it tells the database exactly how the two tables relate, match up each post's author_id with a users row whose id equals it. The result is a combined table where every post title now sits next to the actual username of whoever wrote it.

Why not just store the username directly in posts?

If a user later changes their username, storing only the id means every one of their posts automatically shows the updated name, with nothing else to fix. If you had copied the username text into every post instead, you would have to update every single post whenever a username changed. Storing an id and joining is what keeps your data as a single source of truth.

Another everyday example

An online shop might have an orders table storing a customer_id, and a customers table storing each customer's actual name and email:

SELECT orders.id, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.id;

This lists every order alongside the actual name of the customer who placed it, even though the orders table itself never stored a name directly.