Four Kinds of Joins

This is one of those topics that you will probably come back to again and again in the future.

Joins are powerful — but they can also be confusing at first.

So in this lesson, we are going to understand four different kinds of joins:

1. Inner Join

2. Left Outer Join

3. Right Outer Join

4. Full Join

Up until now, we have only been using something called an Inner Join.

Now it’s time to understand what that really means — and how the other joins are different.

---

1️⃣ Inner Join (The Default Join)

Whenever you write:

JOIN users

That is automatically an INNER JOIN.

You could also write:

INNER JOIN users

Both are 100% the same.

How Inner Join Works

Inner Join merges two tables together, but:

• If a row does NOT match in both tables → it is removed.

• Only matching rows are kept.

Let’s use our example:

• We have a photos table.

• We have a users table.

We try to match:

photos.user_id = users.id

If a photo has a valid user_id → it matches → it stays.

If a photo has user_id = NULL → it does not match → it is removed.

If a user has no photos → that user is also removed.

So Inner Join keeps only the rows that match on both sides.

That’s why it’s called “inner” — it keeps only the overlapping part.

---

2️⃣ Left Outer Join

Now let’s talk about Left Join.

You write it like this:

LEFT JOIN users

How Left Join Works

Left Join means:

• Keep everything from the LEFT table.

• Try to match rows from the RIGHT table.

• If no match is found → fill with NULL values.

• Do NOT remove the left-side row.

In our example:

• photos is the left table (because it comes after FROM).

• users is the right table.

So with a LEFT JOIN:

• Every photo will appear.

• If a matching user exists → attach it.

• If no matching user exists → user columns become NULL.

So that photo with user_id = NULL?
It will now appear in the result.

This is exactly what we needed in our previous problem.

---

3️⃣ Right Outer Join

Right Join is basically the opposite of Left Join.

You write:

RIGHT JOIN users

How Right Join Works

Right Join means:

• Keep everything from the RIGHT table.

• Try to match rows from the LEFT table.

• If no match → fill left-side columns with NULL.

• Do NOT remove the right-side row.

In our example:

• All users will appear.

• If a user has photos → they match.

• If a user has no photos → photo columns become NULL.

So unmatched photos are removed.
Unmatched users are kept.

---

4️⃣ Full Join

Finally, we have Full Join.

You write:

FULL JOIN users

How Full Join Works

Full Join means:

• Keep everything from both tables.

• Match rows where possible.

• If no match → fill missing side with NULL.

So:

• All photos appear.

• All users appear.

• If they match → merged.

• If they don’t match → still included with NULL values.

Full Join basically says:

“Just give me everything.”

---

When Would We Use Each?

It depends on the question we are trying to answer.

For example:

Earlier, we wanted:

Show every single photo, even if it has no related user.

In that case:

• photos is our source table.

• So we should use a Left Join.

That way:

• All photos stay.

• Matching users are attached.

• Unmatched photos still appear.

---

Quick Summary

Join Type	What It Keeps
INNER JOIN	Only matching rows
LEFT JOIN	All rows from left table
RIGHT JOIN	All rows from right table
FULL JOIN	All rows from both tables

---

Final Thoughts

This is your first introduction to joins.

It can feel complicated at first — and that’s completely normal.

The most important thing to remember is:

The type of join you use depends on the question you are trying to answer.

If rows are “missing” from your result,
it’s often because you used an Inner Join when you actually needed a Left Join.

In the next lesson, we will see a practical example of using a Left Join to fix our original problem.