SQL Joins Explained with Real Examples

Joins are where SQL stops being a fancy spreadsheet query and starts being a real tool for working with related data. They are also where a lot of developers quietly lose confidence. This guide explains SQL joins with small, concrete examples so the mental model actually sticks.

The setup

Imagine two tables. customers has an id and a name. orders has an id, a customer_id, and a total. A join lets you combine rows from both based on how they relate — here, matching orders.customer_id to customers.id.

INNER JOIN: only the matches

The most common join returns only rows that have a match in both tables:

SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

This gives you every customer who has placed an order, paired with each order. A customer with no orders simply does not appear, and an order with no valid customer does not either. When people say “join” without qualifying it, this is usually what they mean.

LEFT JOIN: keep everyone on the left

Sometimes you want all rows from the first table even when there is no match. That is a LEFT JOIN:

SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

Now every customer shows up. Those without orders still appear, with NULL in the total column. This is exactly how you answer questions like “which customers have never ordered?” — left join, then filter for WHERE o.id IS NULL.

RIGHT and FULL joins

A RIGHT JOIN is the mirror image: all rows from the second table, matched where possible. In practice most people just flip the table order and use LEFT JOIN instead, because it reads more naturally. A FULL OUTER JOIN keeps unmatched rows from both sides — useful for reconciliation, though not every database supports it.

The mistake to watch for

Forget the ON clause and some databases will happily give you a cross join — every row of the first table paired with every row of the second. With a thousand customers and a thousand orders, that is a million rows and a very confused developer. Always be explicit about how the tables relate.

Joining three or more tables

Real queries rarely stop at two tables, and the good news is that joins simply chain. Add an order_items table (with order_id, product_id, quantity) and a products table, and you can walk the whole relationship in one query:

SELECT c.name, o.id AS order_id, p.title, oi.quantity
FROM customers c
INNER JOIN orders o       ON o.customer_id = c.id
INNER JOIN order_items oi ON oi.order_id   = o.id
INNER JOIN products p     ON p.id          = oi.product_id
WHERE c.id = 42;

Read it as a path: customer → their orders → each order’s line items → each item’s product. Every join adds one hop. The trick that keeps multi-joins manageable is short, consistent aliases (c, o, oi, p) and writing each ON clause immediately so you never lose track of how the current table connects. When a query grows past four or five joins, that’s often a hint some of the logic wants to be a view or a CTE with a name.

The LEFT JOIN trap: filters in WHERE vs ON

Here’s the subtle bug that catches even experienced developers. Say you want all customers, plus their orders from this year. This looks right but isn’t:

-- WRONG: silently becomes an INNER JOIN
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= '2026-01-01';

Customers with no orders get NULL for o.created_at, the WHERE clause filters NULL out, and your “keep everyone” join quietly stops keeping everyone. The fix is putting the condition in the join itself:

-- RIGHT: the filter is part of the match condition
LEFT JOIN orders o ON o.customer_id = c.id
                  AND o.created_at >= '2026-01-01'

The rule to memorize: with a LEFT JOIN, conditions on the right table belong in ON; conditions on the left table belong in WHERE. Once you’ve been bitten by this once, you check for it in every review.

Joins and aggregates: counting per customer

The other everyday pattern is joining then grouping — “how many orders does each customer have?” — and it has its own classic mistake:

SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Two details carry the correctness. Using COUNT(o.id) — not COUNT(*) — makes customers with zero orders show 0 instead of 1, because COUNT of a column skips NULLs while COUNT(*) counts the row the LEFT JOIN produced. And watch out when counting joins across multiple one-to-many tables at once: the row multiplication inflates counts, and you’ll want COUNT(DISTINCT ...) or separate subqueries. If a report’s numbers ever look mysteriously doubled, a fan-out join is the first suspect.

Frequently asked questions

Are joins slow? Not inherently — databases have spent fifty years optimizing them. Joins get slow when the columns being matched aren’t indexed. Rule of thumb: every foreign key column (like orders.customer_id) deserves an index; with one, joining millions of rows is routine.

What’s the difference between JOIN and INNER JOIN? Nothing — JOIN is shorthand for INNER JOIN. Writing the full form is a nice kindness for readers who are still building confidence with the distinctions.

Can a table join to itself? Yes — a self join, done by aliasing the same table twice. The classic case is an employees table with a manager_id column: FROM employees e JOIN employees m ON m.id = e.manager_id pairs each employee with their manager. Same mechanics, one table wearing two aliases.

The takeaway

Ninety percent of real work uses just two of these: INNER JOIN when you want matches, and LEFT JOIN when you want to keep everything from one side. Get comfortable picturing which rows survive each one and SQL joins stop being intimidating — they become the everyday tool they were always meant to be.