SQL Joins With Real Tables, Not Venn Diagrams

Every SQL joins tutorial uses abstract "Table A" and "Table B" examples with three rows each. That's fine for the theory, but it doesn't help when you're staring at a real database with users, orders, products, and order_items tables trying to figure out why your report shows the wrong numbers.

Here's the setup we'll use throughout. These are small enough to trace by hand but realistic enough to actually matter.

The Tables

-- users

id
name
city

1
Priya Sharma
Mumbai
2
James Chen
Toronto
3
Sara Ahmed
Cairo
4
Luis Herrera
Bogota


-- orders

id
user_id
total
created_at

10
1
49.99
2026-01-15
11
1
129.00
2026-02-03
12
2
75.50
2026-01-20
13
NULL
15.00
2026-03-01


-- products

id
name
price

1
Keyboard
49.99
2
Monitor
299.00
3
USB Hub
25.50

Notice: Sara and Luis have no orders. Order #13 has no user (maybe a guest checkout, maybe bad data). These edge cases are exactly where joins get interesting.

INNER JOIN: Only Matching Rows

SELECT users.name, orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;

Result:

| name         | total  |
|--------------|--------|
| Priya Sharma | 49.99  |
| Priya Sharma | 129.00 |
| James Chen   | 75.50  |

Sara and Luis vanish -- they have no matching orders. Order #13 vanishes -- it has no matching user. INNER JOIN is strict: both sides must match.

When to use it: When you only care about records that exist in both tables. "Show me customers who have placed orders" is an inner join.

LEFT JOIN: Keep Everything From the Left Table

SELECT users.name, orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

Result:

| name          | total  |
|---------------|--------|
| Priya Sharma  | 49.99  |
| Priya Sharma  | 129.00 |
| James Chen    | 75.50  |
| Sara Ahmed    | NULL   |
| Luis Herrera  | NULL   |

Every user appears, even if they have no orders. The order columns just show NULL for Sara and Luis. Order #13 still doesn't appear because it's on the right side.

When to use it: "Show me all users and their orders, including users who haven't ordered anything." This is probably the most common join in application code. You want to preserve the "primary" table and optionally attach related data.

A common pattern -- finding rows with no match:

SELECT users.name
FROM users
LEFT JOIN orders ON users.id = orders.user_id
WHERE orders.id IS NULL;

This gives you Sara and Luis -- users who've never placed an order. Extremely useful for "find inactive users" type queries.

RIGHT JOIN: The Mirror Image

SELECT users.name, orders.id, orders.total
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

Result:

| name         | id | total  |
|--------------|----|--------|
| Priya Sharma | 10 | 49.99  |
| Priya Sharma | 11 | 129.00 |
| James Chen   | 12 | 75.50  |
| NULL         | 13 | 15.00  |

Now every order appears, including #13 with its NULL user. Sara and Luis disappear because they're on the left side with no match.

In practice, RIGHT JOIN is rarely used. You can always rewrite it as a LEFT JOIN by swapping the table order. Most codebases stick to LEFT JOIN for consistency.

FULL OUTER JOIN: Keep Everything

SELECT users.name, orders.id, orders.total
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;

Result:

| name          | id   | total  |
|---------------|------|--------|
| Priya Sharma  | 10   | 49.99  |
| Priya Sharma  | 11   | 129.00 |
| James Chen    | 12   | 75.50  |
| Sara Ahmed    | NULL | NULL   |
| Luis Herrera  | NULL | NULL   |
| NULL          | 13   | 15.00  |

Every row from both tables appears. NULLs fill in wherever there's no match. This is useful for reconciliation -- "show me everything, including orphaned records on both sides."

Note: MySQL doesn't support FULL OUTER JOIN directly. You'd need to UNION a LEFT JOIN and a RIGHT JOIN.

CROSS JOIN: Every Combination

SELECT users.name, products.name AS product
FROM users
CROSS JOIN products;

This produces 4 users x 3 products = 12 rows. Every user paired with every product. No ON clause -- there's no matching condition.

When to use it: Generating combinations. "Create a row for every user-product pair so we can track whether they've reviewed each product." It's also useful for generating date ranges or grid data. Just be careful -- cross joining two 10,000-row tables gives you 100 million rows.

Performance: What Actually Matters

Indexes on join columns are critical. If you're joining orders.user_id to users.id, you need an index on orders.user_id. Without it, the database does a full table scan for every row. On tables with millions of rows, this is the difference between 50ms and 50 seconds. Join order matters less than you think. Modern query optimizers (PostgreSQL, MySQL 8+) rearrange your joins for you. Writing FROM users JOIN orders vs FROM orders JOIN users usually produces the same execution plan. But if you're dealing with a stubborn optimizer, check the query plan with EXPLAIN ANALYZE. Be careful with joins that multiply rows. If a user has 5 orders and each order has 3 items, joining users-orders-order_items gives you 15 rows per user. If you then SUM the order totals, you'll count each total 3 times. This is a common source of "my numbers are wrong" bugs. Use subqueries or CTEs to aggregate before joining when this happens. LEFT JOINs aren't inherently slower than INNER JOINs. The performance difference is usually negligible. Pick the join type based on what data you need, not performance.

Practice With Real Queries

The only way joins really click is by writing them against actual data and seeing what comes back. CodeUp has SQL challenges where you can run queries against real tables in the browser -- no database setup required. Start with simple two-table joins and work up to multi-table queries with aggregation.

The moment joins stop being confusing is the moment you stop thinking about them as abstract operations and start thinking about them as questions: "Which rows do I need? What happens to rows that don't match? Do I care about those?" Answer those three questions and the join type picks itself.