SQL INNER JOIN: Syntax, Examples, and Interview Patterns

-- Basic INNER JOIN
SELECT
  o.order_id,
  o.order_date,
  c.name AS customer_name,
  c.email
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

-- Join with additional filter
SELECT
  o.order_id,
  o.total,
  p.name AS product_name,
  p.category
FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.order_date >= '2024-01-01'
  AND p.category = 'electronics';

Interview note: Interviewers watch where you put filters. Conditions that belong in WHERE (like date ranges or category filters) should not go in the ON clause. ON defines the relationship between tables. WHERE filters the result. Mixing them up changes results with LEFT JOIN and signals confusion about join semantics.

-- INNER JOIN: only orders with valid customers
SELECT o.order_id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;
-- Returns 950 rows (50 orphaned orders dropped)

-- LEFT JOIN: all orders, NULLs for missing customers
SELECT o.order_id, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id;
-- Returns 1000 rows (50 rows have NULL name)

Interview note: A common follow-up: 'Your query returns fewer rows than expected. What happened?' The answer is almost always an INNER JOIN dropping unmatched rows. State that you would check for NULLs in the join column or switch to LEFT JOIN to debug.

-- 4-table join: orders -> order_items -> products -> categories
SELECT
  c.name AS customer,
  o.order_id,
  o.order_date,
  p.name AS product,
  cat.name AS category,
  oi.quantity,
  oi.unit_price,
  oi.quantity * oi.unit_price AS line_total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
INNER JOIN categories cat ON p.category_id = cat.id
WHERE o.order_date >= '2024-01-01'
ORDER BY o.order_date DESC, line_total DESC;

-- BUG: order total is counted once per line item
SELECT
  c.name,
  SUM(o.total) AS revenue  -- WRONG: inflated by item count
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.name;

-- FIX: aggregate line items, not order totals
SELECT
  c.name,
  SUM(oi.quantity * oi.unit_price) AS revenue  -- CORRECT
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.name;

Interview note: The 'inflated SUM after JOIN' bug is a top-5 interview trap. When you join to a one-to-many table, any aggregate on the 'one' side gets counted multiple times. The fix is to either aggregate at the correct grain or use a subquery/CTE to pre-aggregate before joining.