Joins in SQL

One of the annoying parts of working with a relational database is having to pull data back together across tables. Here's the quick version: how the users and posts tables relate, and how a single JOIN stitches them into one result.

Watch: Joins in SQL

The setup: two tables

We have a users table (username, email, name) and a posts table. Each post carries a user_id that references users(user_id) as a foreign key — so one user can have many posts. On its own, SELECT * FROM posts gives you that bare user_id and none of the user's actual details.

Entity-relationship diagram: users table (user_id PK) has many posts; posts table carries a user_id foreign key plus post_id, title, body — How the tables relate: one user has many posts, linked by the user_id foreign key.

The query

The schema and the join look like this:

SQL

-- users: one row per person
CREATE TABLE users (
    user_id  SERIAL PRIMARY KEY,
    username VARCHAR(50),
    email    VARCHAR(255),
    name     VARCHAR(100)
);

-- posts: each post references a user via a foreign key
CREATE TABLE posts (
    post_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id),
    title   TEXT,
    body    TEXT
);

-- Without a join you only get the bare user_id, no user detail
SELECT * FROM posts;

-- Join pulls each post together with its user's info
SELECT *
FROM posts
JOIN users ON posts.user_id = users.user_id;

The JOIN ... ON matches rows where the two user_id values are equal, so every post comes back with its user's full details attached.