SQL Joins Explained With Examples

Reviewed & published by Brayan K

Master INNER, LEFT, RIGHT and FULL joins with practical SQL examples, visual tables, and real-world scenarios.

Master INNER, LEFT, RIGHT, and FULL joins with practical SQL examples and visual explanations.

Introduction

If you're learning SQL, one of the most important things you'll ever understand is JOINS. They're the backbone of combining data across multiple tables — which is how real databases actually work.

This guide breaks down SQL joins in a simple, visual, example-driven way so you can finally understand:

✔ How each JOIN works
✔ When to use which JOIN
✔ How LEFT, RIGHT, INNER, and FULL JOIN differ
✔ Real-world examples from business databases

Let's make SQL joins easy.

1. What Are SQL Joins?

A JOIN allows you to combine rows from two or more tables based on a related column.

Orders table

Without joins, each table is isolated.

With joins, tables become powerful relational data sources.

2. Example Tables Used in This Guide

Table: users

user_id	name
1	Alice
2	Bob
3	Charlie

Table: orders

order_id	user_id	product
101	1	Laptop
102	1	Mouse
103	2	Keyboard

3. INNER JOIN (Most Common)

Returns matching rows from both tables.

Result:

name	product
Alice	Laptop
Alice	Mouse
Bob	Keyboard

🧠 Charlie has no orders → not included.

You only want records that exist in both tables.

4. LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from LEFT table + matching rows from RIGHT. Missing matches become NULL.

name	product
Alice	Laptop
Alice	Mouse
Bob	Keyboard
Charlie	NULL

🧠 LEFT = users, so all users appear, even those with no orders.

You want the "full list" of the primary table.

Show all customers, even if they haven't ordered
Show all products, even if none were sold

5. RIGHT JOIN (RIGHT OUTER JOIN)

Opposite of LEFT JOIN. Returns ALL rows from the RIGHT table + matches from LEFT.

Same data as INNER JOIN in this example, because all orders have matching users:

name	product
Alice	Laptop
Alice	Mouse
Bob	Keyboard

Right table is the "main" table.

Show all orders, even if the user was deleted
Show all shipped items, even if not linked to a customer yet

6. FULL OUTER JOIN

Returns ALL rows from both tables. Where no match exists → NULLs appear.

name	product
Alice	Laptop
Alice	Mouse
Bob	Keyboard
Charlie	NULL
NULL	NULL

(The last NULL row only appears if orders exist with no matching user.)

You want everything from both sides.

👉 Great for auditing or data cleanup.

7. CROSS JOIN

Produces the Cartesian product of both tables. Every row in table A combines with every row in table B.

If 3 users × 3 orders → 9 rows.

Generating combinations
Time series grids
Matrix-style reporting

Avoid for large tables — can explode rows.

8. SELF JOIN

A table joined with itself.

Useful for hierarchies like:

Employees → Managers
Categories → Subcategories
Friends → Mutual friends

10. When to Use Which JOIN (Quick Guide)

JOIN	When to Use
INNER JOIN	Keep only matches
LEFT JOIN	Keep everything on left
RIGHT JOIN	Keep everything on right
FULL JOIN	Keep everything from both
CROSS JOIN	All combinations
SELF JOIN	Hierarchies or relationships

Conclusion

SQL joins are the foundation of working with relational databases. Once you understand how each join behaves — and when to use them — you unlock the ability to:

✔ Build better queries
✔ Analyse data faster
✔ Solve real business problems
✔ Ace SQL interview questions
✔ Work confidently as a database developer

SQL Database Design Best Practices — Learn how to design efficient, scalable database schemas that will stand the test of time.
Database Indexing Strategies — Optimize your SQL queries with proper indexing techniques and strategies.