SQL INNER JOIN

Source: DataDriven analysis of 1,042 verified data engineering interview rounds.

INNER JOIN Syntax

Three moving parts: left table, right table, ON clause. The ON clause resolves roughly 95% of the time to a foreign-key to primary-key comparison, but the language accepts any boolean expression. The part interviewers watch is whether you know which side you put in the driving position.

-- 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;

The INNER keyword is optional. Writing JOIN alone defaults to INNER JOIN. But in queries that mix join types, writing it explicitly prevents confusion.

How the Engine Processes It

-- 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';

INNER JOIN vs Other Joins

Each join type handles unmatched rows differently. This table shows exactly what happens to rows that exist in one table but not the other.

The critical distinction: INNER JOIN is lossy. It drops rows that do not match. If your orders table has 1,000 rows but only 950 have valid customer_ids, INNER JOIN with customers returns 950 rows. Those 50 orphaned orders vanish from the result. LEFT JOIN would keep them with NULL customer columns.

-- 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)

Multi-Table INNER JOIN

Production queries rarely join just two tables. A typical analytics query might join orders to customers, then to products through an order_items bridge table, then to regions. Each additional JOIN adds another ON clause and another opportunity for row multiplication or row loss.

-- 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;

Row Count Behavior

When you join through a bridge table (like order_items), the output has one row per line item, not one row per order. An order with 3 items produces 3 output rows. An order with 10 items produces 10. This is correct behavior for a line-item report, but it breaks aggregate calculations if you are not careful.

-- 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;

INNER JOIN Performance

Join performance depends on three things: the join algorithm the engine picks, whether the join columns are indexed, and the cardinality of the tables. Here is what matters in practice.

-- Ensure the FK column is indexed
CREATE INDEX idx_orders_customer_id
ON orders (customer_id);

-- Now this join uses an index lookup instead of a full scan
SELECT o.order_id, c.name
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

5 INNER JOIN Interview Questions

These questions appear in phone screens and onsites at companies from startups to FAANG. The first two are warmups. The rest test deeper understanding.

3 INNER JOIN Mistakes That Fail Interviews

These come up repeatedly in interview feedback. Each one is easy to avoid once you know the pattern.

-- WRONG: drops customers with zero orders
SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

-- CORRECT: keeps all customers
SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

-- BUG: joining on date creates many-to-many
SELECT a.*, b.*
FROM daily_sales a
INNER JOIN daily_expenses b ON a.date = b.date;
-- If 50 sales rows and 30 expense rows share the same date,
-- that date produces 50 * 30 = 1500 rows

-- FIX: aggregate before joining
WITH sales AS (
  SELECT date, SUM(amount) AS total_sales
  FROM daily_sales GROUP BY date
),
expenses AS (
  SELECT date, SUM(amount) AS total_expenses
  FROM daily_expenses GROUP BY date
)
SELECT s.date, s.total_sales, e.total_expenses
FROM sales s
INNER JOIN expenses e ON s.date = e.date;

-- INNER JOIN: both produce the same result
-- But the second is clearer about intent
SELECT o.order_id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
  AND c.country = 'US';  -- filter in ON

SELECT o.order_id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'US';  -- filter in WHERE (preferred)

SQL INNER JOIN FAQ

Related Guides