INNER JOIN

Combines rows from two tables where the join condition is true; unmatched rows are dropped.

Syntax

SELECT ...
FROM a
[INNER] JOIN b ON b.a_id = a.id
[JOIN c ON ...]

FROM a JOIN b USING (shared_column)

An inner join pairs every row of a with every row of b that satisfies ON, and keeps only those pairs.

Rows with no partner on the other side disappear. Use LEFT JOIN if they must stay.
If a row matches several rows on the other side, it appears several times (one-to-many). Summing a column of the "one" side after such a join counts it repeatedly — the fan-out trap. Aggregate the many-side first.
USING (col) joins on equally named columns and outputs that column once; ON is more explicit.
Qualify columns with table aliases (o.customer_id) whenever both tables have a column of that name; otherwise you get column reference ... is ambiguous.
NULL: NULL = NULL is not true, so rows with a NULL join key never match.
Join on keys (ids), not on names or other non-unique text.

Examples

Orders with customer and product names starter

SELECT o.order_id, c.customer_name, p.product_name, o.quantity
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN products p ON p.product_id = o.product_id
ORDER BY o.order_id;

Each order matches exactly one customer and one product, so the row count stays the number of orders.

Joining through a bridge table shop

SELECT cat.category_name, round(sum(oi.quantity * oi.unit_price - oi.discount_amount), 2) AS revenue
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
JOIN categories cat ON cat.category_id = p.category_id
GROUP BY cat.category_name
ORDER BY revenue DESC;

order_items is the many-to-many bridge between orders and products.

The fan-out trap, fixed shop

WITH items AS (
  SELECT order_id, sum(quantity * unit_price - discount_amount) AS items_value
  FROM order_items GROUP BY order_id
), paid AS (
  SELECT order_id, sum(amount) AS captured
  FROM payments WHERE status = 'captured' GROUP BY order_id
)
SELECT o.status, count(*) AS orders,
       round(sum(i.items_value + o.shipping_fee), 2) AS order_value,
       round(sum(p.captured), 2) AS captured
FROM orders o
JOIN items i USING (order_id)
LEFT JOIN paid p USING (order_id)
WHERE o.status = 'delivered'
GROUP BY o.status;

Each side is aggregated to one row per order before joining, so nothing is double counted; for delivered orders the two totals agree.

Pitfalls

Losing rows silently because the other side has no match (use LEFT JOIN).
Inflated sums after joining two one-to-many tables to the same parent.
Missing or wrong ON condition producing a near-Cartesian product.
Ambiguous column names: qualify with aliases.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

Oracle: Table aliases cannot use AS (FROM orders o, not FROM orders AS o). Old code uses (+) for outer joins.