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
MSMuhammad SufiyanSoftware Engineer · 5d ago
Backend Engineering HubT-
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.
HIRINGMINE CAREER SIGNAL
This writing is proof of expertise.
Explore the author’s verified skills, projects and availability—or start a professional conversation.
