Written with a little help from AI, reviewed and edited by a human.
In the previous post we learned how to store, read, update, and delete data from a single table. But in real applications, data is usually spread across multiple tables. In this post we’ll learn how to combine data from different tables using JOINs.
Why do we need JOINs?
Let’s say we’re building a simple blog. We have a table for users and a table for posts. Each post was written by a user.
users table
| id | name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
posts table
| id | title | user_id |
|---|---|---|
| 1 | My first post | 1 |
| 2 | Hello world | 1 |
| 3 | Learning SQL | 2 |
Notice that the posts table has a column called user_id. This is how we connect a post to the user who wrote it. Alice (id 1) wrote two posts, Bob (id 2) wrote one post, and Charlie (id 3) hasn’t written any posts yet.
Now, what if we want to see a list of posts with the name of the author next to each one? That data lives in two different tables. We can’t get it with a simple SELECT. This is where JOINs come in — they let us combine rows from two tables based on a related column.
Real life example
Before we look at any SQL, let’s think about JOINs using a real life example.
Imagine you’re a teacher and you have two lists:
List 1 — Students:
| Student ID | Name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
List 2 — Test Scores:
| Student ID | Score |
|---|---|
| 1 | 85 |
| 2 | 92 |
Charlie didn’t take the test, so he has no score.
Now you want to create one combined list that shows each student’s name next to their score. You would look at each student, find their matching score by Student ID, and put them together. That’s exactly what a JOIN does.
But what happens with Charlie? He has no score. Depending on what kind of JOIN you use, Charlie either shows up in the result (with an empty score) or gets left out. That’s the difference between the types of JOINs.
INNER JOIN
An INNER JOIN returns only the rows that have a match in both tables. If there’s no match, the row is left out completely.
Going back to our teacher example — an INNER JOIN would give you:
| Name | Score |
|---|---|
| Alice | 85 |
| Bob | 92 |
Charlie is not included because he has no matching score.
In SQL this looks like:
SELECT users.name, posts.title
FROM users
INNER JOIN posts ON users.id = posts.user_id;Let’s break this down:
SELECT users.name, posts.title— We want the name from the users table and the title from the posts table.FROM users— We start with the users table.INNER JOIN posts— We want to combine it with the posts table.ON users.id = posts.user_id— This is the condition that connects the two tables. We’re saying: “match rows where the user’s id equals the post’s user_id.”
The result would be:
| name | title |
|---|---|
| Alice | My first post |
| Alice | Hello world |
| Bob | Learning SQL |
Charlie is not in the result because he has no posts. Only users with matching posts appear.
💡 Think of INNER JOIN as: “show me only the people who appear on both lists.”
LEFT JOIN
A LEFT JOIN returns all rows from the left table (the first table), and the matching rows from the right table. If there’s no match, the right side will just be empty (NULL).
Going back to our teacher example — a LEFT JOIN would give you:
| Name | Score |
|---|---|
| Alice | 85 |
| Bob | 92 |
| Charlie | NULL |
Charlie is included this time, but his score is NULL because he has no matching score.
In SQL:
SELECT users.name, posts.title
FROM users
LEFT JOIN posts ON users.id = posts.user_id;The result would be:
| name | title |
|---|---|
| Alice | My first post |
| Alice | Hello world |
| Bob | Learning SQL |
| Charlie | NULL |
Every user appears, even if they haven’t written a post. Charlie shows up with NULL for the title.
💡 Think of LEFT JOIN as: “show me everyone from the left list, and fill in what you can from the right list.”
RIGHT JOIN
A RIGHT JOIN is the opposite of a LEFT JOIN. It returns all rows from the right table (the second table), and the matching rows from the left table. If there’s no match on the left side, those values will be NULL.
Let’s use a slightly different example. Imagine we have some posts where the author was deleted from the users table:
users table
| id | name |
|---|---|
| 1 | Alice |
posts table
| id | title | user_id |
|---|---|---|
| 1 | My first post | 1 |
| 2 | Mystery post | 99 |
User 99 doesn’t exist anymore but their post is still there.
SELECT users.name, posts.title
FROM users
RIGHT JOIN posts ON users.id = posts.user_id;The result would be:
| name | title |
|---|---|
| Alice | My first post |
| NULL | Mystery post |
Every post appears, even if the author no longer exists in the users table.
💡 Think of RIGHT JOIN as: “show me everyone from the right list, and fill in what you can from the left list.”
In practice, most developers just use LEFT JOIN and swap the table order instead of using RIGHT JOIN. Both work, it’s just a matter of preference.
Quick summary
Here’s a simple way to remember the different JOINs:
- INNER JOIN — Only rows that match in both tables. Like a guest list — you only get in if you’re on both lists.
- LEFT JOIN — All rows from the left table, even if there’s no match on the right. Like taking attendance — everyone on your class list shows up, even if they didn’t submit homework.
- RIGHT JOIN — All rows from the right table, even if there’s no match on the left. Same idea as LEFT JOIN but from the other side.
When starting out, you’ll use INNER JOIN and LEFT JOIN the most. They cover the vast majority of real-world cases. As you get more comfortable with SQL, you’ll naturally learn when to use which one.