Mastering SQL Joins: An Interactive Guide

SQL Joins are often the first major stumbling block for beginners. Venn diagrams help, but nothing beats actually running the code and seeing what happens to the data.

In this interactive guide, we'll explore the most common types of joins using a simple dataset of Users and Orders.

The Setup

We have two tables:

1. users: A list of registered users.
2. orders: A list of orders placed by those users.

Here is the data we will be working with:

• Users: Alice (ID 1), Bob (ID 2), Charlie (ID 3)
• Orders: Order 101 (by Alice), Order 102 (by Alice), Order 103 (by David - wait, David isn't in the users table!)

1. INNER JOIN

The INNER JOIN is the most common join. It returns only the rows where there is a match in BOTH tables.

If a user hasn't placed an order, they won't show up. If an order references a user ID that doesn't exist, it won't show up.

Visualizing INNER JOIN:

Try it yourself:

Observation:

• Alice appears twice because she has two orders.
• Bob and Charlie do NOT appear because they have no orders.
• The "Ghost Item" (Order 103) does NOT appear because user ID 99 doesn't exist in the users table.

2. LEFT JOIN

The LEFT JOIN (or LEFT OUTER JOIN) returns ALL rows from the left table (users), and the matching rows from the right table (orders). If there is no match, the result is NULL on the right side.

This is perfect for finding users who haven't bought anything yet.

Visualizing LEFT JOIN:

Observation:

• Alice is still there with her orders.
• Bob and Charlie are now visible! But their item column is NULL because they haven't placed any orders.

Conclusion

• Use INNER JOIN when you only want records that have a relationship.
• Use LEFT JOIN when you want to keep everything from the primary table, even if it has no related records.

Go ahead and modify the queries above to experiment! Try changing LEFT JOIN to CROSS JOIN and see what happens (but be careful with large tables!).