JOINs in SQL — Combining Tables Like a Pro

What You Will Learn

• Why JOINs exist and what they solve
• The four JOIN types: INNER, LEFT, RIGHT, FULL
• How to read a JOIN query line by line
• How to join more than two tables

Why This Topic Matters

Real databases are split across many tables — one for customers, one for orders, one for products. To answer almost any business question, you must combine them. JOIN is the SQL way to do that. Without JOINs, you are limited to analyzing one table at a time — which means you cannot do real analytics.

The Two Tables

Two small tables. customers on the left, orders on the right. They are linked by customer_id.

customers

orders

Notice: Mira (C03) and Sam (C04) have no orders. Order 004 references customer C05, who does not exist in the customers table. These "gaps" are exactly what JOIN types handle differently.

INNER JOIN — Match Only

SELECT c.name, c.city, o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

Result:

Only rows where the customer_id appears in BOTH tables. Mira and Sam are dropped (no orders). Order 004 is dropped (no matching customer). This is the strictest JOIN — "show me only the matches".

Line by line

• FROM customers c — start with the customers table; alias it as c.
• INNER JOIN orders o — combine with the orders table; alias as o.
• ON c.customer_id = o.customer_id — the matching rule: when the customer_id matches in both tables, the rows are joined.
• SELECT c.name, c.city, o.amount — pick columns from either table; prefix with the alias to disambiguate.

LEFT JOIN — All Left Rows, Matched Where Possible

SELECT c.name, c.city, o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Result:

Every customer appears. Where there is no matching order, the order columns are NULL. Use LEFT JOIN when you want to find customers who have not ordered yet (the NULL rows).

Finding customers with no orders

SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Returns Mira and Sam. This is one of the most useful patterns in analytics: "show me what's missing".

RIGHT JOIN — All Right Rows

SELECT c.name, c.city, o.amount
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

Result:

Every order appears. Order 004 has no matching customer, so the customer columns are NULL. RIGHT JOIN is rarely used in practice — most people just swap the tables and use LEFT JOIN, which is more readable.

FULL OUTER JOIN — Everything From Both Sides

SELECT c.name, c.city, o.amount
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

Result:

Everyone and everything — matched where possible, NULL where not. Useful for data audits: "show me all customers and all orders, whether or not they match".

Note: SQLite does not support FULL OUTER JOIN directly. You can simulate it with a UNION of LEFT JOIN and RIGHT JOIN (which itself must be rewritten as a LEFT JOIN with swapped tables).

The Four JOINs at a Glance

Joining More Than Two Tables

Add a products table linked by product_id in orders. To join all three:

SELECT c.name, p.name AS product, o.amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN products p  ON o.product_id = p.product_id;

Each JOIN adds one table and one ON condition. Most real queries join 3–5 tables. Beyond that, consider simplifying the schema or using views.

Start with the "central" table

Notice we started FROM orders and joined outward to customers and products. The orders table is the "fact table" — it connects to multiple "dimension tables". This pattern (fact in the middle, dimensions around) is called a star schema and is the standard way analytics databases are organized.

CROSS JOIN — The Cartesian Product (Rare)

SELECT *
FROM customers
CROSS JOIN products;

Returns every combination of customer and product — usually nonsense for analysis, but useful for generating all possible combinations (e.g., "every customer × every product" for a coverage report).

Common Mistakes

1. Forgetting the ON clause. Without ON, INNER JOIN becomes a CROSS JOIN in some databases and an error in others. Always specify ON.
2. Joining on the wrong column. ON c.customer_id = o.order_id joins unrelated IDs. The result will be empty or nonsense. Always sanity-check the join condition.
3. Ambiguous column names. If both tables have a column called id, write c.id and o.id, never just id.
4. Using INNER when you mean LEFT. INNER silently drops non-matching rows. If you need to know about customers with no orders, use LEFT.
5. Not aliasing tables. customers.customer_id works but is verbose. Use short aliases like c and o.
6. Joining on non-unique columns. If the join column has duplicates, you get a row explosion (cartesian-like multiplication). Always check that the join key is unique on at least one side.

Practical Exercise (15 minutes)

1. Create the customers and orders tables above in a SQL tool.
2. Run all four JOINs. Confirm the row counts: INNER = 3, LEFT = 5, RIGHT = 4, FULL = 6.
3. Use LEFT JOIN to find customers who have not placed any order.
4. Add a products table (product_id, name) with 3 products. Add a product_id column to orders. Write a 3-table INNER JOIN.
5. Compute total spending per customer using LEFT JOIN + GROUP BY. (Customers with no orders should show NULL or 0.)

Mini Challenge

Write a query that returns each customer's name alongside the count of orders they have placed, including customers who have placed zero orders. (Hint: LEFT JOIN + GROUP BY + COUNT(o.order_id) — not COUNT(*), because COUNT(*) would return 1 even for customers with no orders.)

Key Takeaways