SQL Joins Explained With Examples
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
1. What Are SQL Joins?
A JOIN allows you to combine rows from two or more tables based on a related column.
- Orders table
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
product
101
Laptop
102
Mouse
103
Keyboard
3. INNER JOIN (Most Common)
Result:
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.
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:
- 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.
(The last NULL row only appears if orders exist with no matching user.)
7. CROSS JOIN
Produces the Cartesian product of both tables. Every row in table A combines with every row in table B.
- Generating combinations
- Time series grids
- Matrix-style reporting
8. SELF JOIN
- Employees → Managers
- Categories → Subcategories
- Friends → Mutual friends
9. Real-World JOIN Examples
📌 Example 1: Get all users and their latest order
📌 Example 2: Find users with no orders
📌 Example 3: Count orders per user
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
Related articles
- 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.
- ⭐ Boost Your Coding Speed With AI Tools — Discover the exact AI tools and strategy that help beginners learn 10x faster while building real apps and websites.