INNER JOIN

INNER JOIN pairs rows from two tables wherever an ON predicate is true, dropping every row on either side that has no match.

SyntaxSELECT ... FROM a INNER JOIN b ON a.col = b.col

An INNER JOIN combines each row of one table with rows of another wherever a condition you supply is true. It exists because a normalised schema splits related facts across tables: an orders row stores a customer_id, not the customer's email. A join is how you put them back together for the length of one query.

ON is a predicate, not a keyword slot

Whatever follows ON is a boolean expression evaluated for a pair of rows. Pairs where it is true are emitted; everything else is discarded, including rows on either side that never found a partner. It is usually an equality on a foreign key, but it can be a range, a comparison against a literal, or several conditions joined with AND.

Shorthand and longhand

MySQL treats JOIN, INNER JOIN and CROSS JOIN as syntactic equivalents — all three parse to the same thing, unlike standard SQL. The old comma form FROM a, b WHERE a.id = b.a_id is the same join written with the predicate in WHERE. And USING (customer_id) is shorthand for ON a.customer_id = b.customer_id, with one difference: USING collapses the two same-named columns into a single coalesced column, so SELECT * returns it once instead of twice.

What trips people up

Because MySQL accepts a join with no ON at all, a forgotten condition is a Cartesian product rather than an error. And NULL = NULL is not true, so rows whose join key is NULL never match anything — use <=> if you genuinely want NULL to match NULL.

Example

Example · sql
-- Orders with the customer who placed them.
-- INNER means rows without a partner vanish from both sides:
-- an order whose customer row was deleted disappears, and so does
-- every customer who has never ordered.
SELECT  o.id           AS order_id,
        o.placed_at,
        o.total_cents,
        c.email
FROM    orders AS o
INNER JOIN customers AS c
        ON c.id = o.customer_id     -- the join predicate
WHERE   o.placed_at >= '2026-01-01' -- filters the joined result
ORDER BY o.placed_at DESC
LIMIT   50;

When to use it

  • Show order rows alongside the customer name and email they belong to.
  • Resolve a foreign key into a human-readable label for a report or export.
  • Restrict a query to rows that have a matching row in a second table, such as products that appear in at least one order.
  • Join a fact table to a versioned price or rate table using a date window in the ON clause.

More examples

USING is shorthand for an equality ON

USING only works when the columns are named identically on both sides, which is the usual case when the foreign key follows the parent's column name. Reach for it to cut noise in multi-column joins, and be ready to recognise it in other people's code; use ON when the names differ or when you need both copies of the column.

Example · sql
-- Longhand: both columns appear in SELECT *
SELECT *
FROM order_items AS oi
INNER JOIN products AS p
        ON oi.product_id = p.product_id;

-- Shorthand: same join, same rows, but the two product_id columns
-- are merged into one coalesced product_id in the output.
SELECT *
FROM order_items
INNER JOIN products USING (product_id);

-- USING takes a list, so this replaces three ANDed equalities:
SELECT *
FROM stock_moves
INNER JOIN stock_levels USING (warehouse_id, product_id, batch_no);

An ON clause that is more than an equality

Shows that ON is an arbitrary predicate, not just a foreign-key equality. This shape appears whenever a dimension is versioned over time — prices, tax rates, exchange rates, employee grades — and it is why the join belongs in ON rather than a post-hoc WHERE filter.

Example · sql
-- product_prices keeps one row per product per validity window.
-- The join has to pick the row whose window contains the order date,
-- so the predicate carries two range tests alongside the key match.
SELECT  oi.order_id,
        p.sku,
        oi.quantity,
        pp.unit_cents
FROM    order_items AS oi
JOIN    orders   AS o ON o.id = oi.order_id
JOIN    products AS p ON p.id = oi.product_id
JOIN    product_prices AS pp
        ON  pp.product_id = p.id
        AND o.placed_at  >= pp.valid_from
        AND o.placed_at  <  pp.valid_to;   -- half-open window, no overlap

Join then aggregate

The usual reporting shape: join to assemble the wide row, WHERE to drop rows before grouping, GROUP BY to collapse, HAVING to filter the collapsed groups. Because this is an inner join, customers with zero paid orders are absent entirely rather than showing a zero — switch to LEFT JOIN if you need them listed.

Example · sql
-- Revenue per customer, from the two tables that hold the halves of it.
SELECT  c.id,
        c.email,
        COUNT(o.id)        AS order_count,
        SUM(o.total_cents) AS revenue_cents
FROM    customers AS c
JOIN    orders    AS o ON o.customer_id = c.id
WHERE   o.status = 'paid'
GROUP BY c.id, c.email
HAVING  SUM(o.total_cents) > 100000   -- filters groups, not rows
ORDER BY revenue_cents DESC;

Related

sql left joinsql join on vs wheresql multi joinsql cross joinsql foreign key

Discussion

  • Be the first to comment on this lesson.