Joins

What is it?

You've split your data across tables on purpose — a users table and an orders table, linked by a user_id foreign key. That's great for avoiding duplication, but eventually you need to ask a question that spans both: "show me each order along with the name of the person who placed it." Neither table alone has both pieces of information.

A join is how SQL answers that. It combines rows from two tables based on a matching column (usually a foreign key matching a primary key), producing results that look like the two tables had been "glued together" side by side, row by row, wherever they match.

The two most common kinds: an INNER JOIN returns only rows that have a match in both tables. A LEFT JOIN returns every row from the left (first) table, filling in empty values when there's no match in the right table.

Explain like I'm 10

Imagine two spreadsheets: one lists employees and their department ID, another lists department IDs and department names. A join is like a coworker who takes both spreadsheets and hands you one combined sheet where every employee row also shows their department's actual name, matched up by that shared ID.

Examples

INNER JOIN — only matching rows

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

Returns one row per order, but only for orders whose user_id actually matches a real user — an order with a broken or missing user_id would be left out entirely.

LEFT JOIN — keep unmatched rows too

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

Returns every user, even ones who have never placed an order — for those, order_id simply comes back as empty (NULL) instead of the row disappearing.

How it works

For each row in the first table, the database looks for row(s) in the second table where the join condition (ON ...) is true, and produces one combined result row for every match found. With INNER JOIN, a row from the first table with zero matches contributes nothing to the results. With LEFT JOIN, a row from the first (left) table with zero matches still appears once, with the second table's columns filled in as empty.

Under the hood, the database often uses an index on the joined column to find matches quickly rather than comparing every row against every other row.

Why does it exist?

Joins exist because splitting data into separate, non-duplicated tables (which is exactly what normalization recommends) would be nearly useless if you couldn't easily bring related pieces back together for a single question. Joins are what make normalized, non-redundant table design actually practical to query.

When to use it

Use a join whenever an answer requires information that's split across two or more related tables — showing an order with the customer's name, listing a blog post with its author, showing a product with its category. Use LEFT JOIN specifically when you want to keep rows from the first table even when there's no match (like showing all users, including ones with zero orders).

When not to use it

If you only ever need data from a single table, a join adds unnecessary complexity and cost. And joining very large tables without a supporting index can be slow — sometimes it's worth reconsidering the schema (or adding an index) rather than joining freely everywhere.

Common mistakes

• Forgetting the ON condition, which produces a 'cross join' — every row from the first table paired with every row from the second, a combinatorial explosion.

• Using INNER JOIN when you actually wanted to keep unmatched rows, silently losing data (like customers with no orders vanishing from the results).

• Not qualifying column names (like using just id instead of orders.id) when both joined tables have a column with the same name, causing ambiguity errors.

Practice exercises

1. Easy:

   Write an INNER JOIN between a products table and a categories table (products has a category_id foreign key) to list each product with its category name.

2. Medium:

   Write a LEFT JOIN that lists every category along with its products, including categories that currently have zero products.

3. Hard:

   Explain what result you'd get from joining two tables with an ON condition that's always true (e.g. ON 1 = 1), and why that's usually a mistake.

Interview questions

What's the difference between an INNER JOIN and a LEFT JOIN?

INNER JOIN returns only rows that have a match in both tables; LEFT JOIN returns every row from the left table, filling in empty values when there's no matching row on the right.

Why are joins necessary if data is split across normalized tables?

Because normalization intentionally avoids duplicating data across tables, and joins are how you recombine related data from those separate tables when a query needs both.

What happens if you write a join without an ON condition?

You get a cross join — every row from the first table paired with every row from the second table, which is almost never what's intended.