SQL INNER JOIN – Matching Rows from Two Tables

INNER JOIN Syntax

SQL

SELECT columns
FROM table_a
INNER JOIN table_b ON table_a.column = table_b.column;

-- Shorthand: JOIN alone defaults to INNER JOIN
SELECT columns
FROM table_a
JOIN table_b ON table_a.column = table_b.column;

💡

Always use table aliases

Assign short aliases (e for employees, d for departments) and prefix every column reference with the alias. This avoids ambiguous column errors and makes multi-table queries much easier to read.

How INNER JOIN Works

The database pairs every row from the left table with every row from the right table that satisfies the ON condition. Rows without a match are discarded. Using the sample tables from the overview:

SQL

SELECT e.id, e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- Result:
-- id | name  | dept_name
--  1 | Alice | Marketing
--  2 | Bob   | Engineering
--  3 | Carol | Engineering
--  4 | Dave  | HR

-- Eve (dept_id = NULL) is EXCLUDED — no matching department
-- Finance department (no employees) is EXCLUDED

SQL

-- Select all columns with prefix to avoid ambiguity
SELECT
  e.id        AS employee_id,
  e.name      AS employee_name,
  e.salary,
  d.id        AS dept_id,
  d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
ORDER BY e.name;

Chaining Multiple Joins

You can chain as many JOINs as needed. Each JOIN adds another table to the result. The order matters for readability but not for correctness (the optimizer handles it).

SQL

-- Three-table join: employees → departments → locations
SELECT
  e.name         AS employee,
  d.dept_name    AS department,
  l.city         AS office_city
FROM employees e
INNER JOIN departments d  ON e.dept_id  = d.id
INNER JOIN locations   l  ON d.location_id = l.id
ORDER BY e.name;

-- Four-table join: orders → customers → products → categories
SELECT
  o.order_id,
  c.name          AS customer,
  p.product_name,
  cat.category_name,
  o.quantity,
  o.quantity * p.price AS line_total
FROM orders o
INNER JOIN customers c   ON o.customer_id  = c.id
INNER JOIN products  p   ON o.product_id   = p.id
INNER JOIN categories cat ON p.category_id = cat.id
WHERE o.order_date >= '2026-01-01'
ORDER BY o.order_date DESC;

Join Conditions

The ON clause can contain any valid condition — not just equality. You can join on ranges, multiple columns, or function results (though non-equality joins can be slow without proper indexing).

SQL

-- Equality join (most common)
JOIN departments d ON e.dept_id = d.id

-- Multi-column join: match on composite key
JOIN order_details od ON o.order_id = od.order_id
                     AND o.store_id  = od.store_id

-- Non-equi join: find salary grades that apply to each employee
SELECT e.name, e.salary, g.grade_name
FROM employees e
INNER JOIN salary_grades g
        ON e.salary BETWEEN g.min_salary AND g.max_salary;

-- USING shorthand when column names match in both tables
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d USING (dept_id);
-- Equivalent to: ON e.dept_id = d.dept_id

Filtering with WHERE vs ON

For INNER JOIN, filtering in ON and in WHERE produces the same result. However, the conventional rule is: put the join condition in ON, and put the row filter in WHERE. For LEFT JOIN (covered next), the distinction is critical.

SQL

-- Recommended: join condition in ON, filter in WHERE
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
WHERE d.dept_name = 'Engineering';

-- Also works but less clear: filter moved into ON
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
                        AND d.dept_name = 'Engineering';

-- Combine aggregation with INNER JOIN
SELECT
  d.dept_name,
  COUNT(e.id)          AS employee_count,
  ROUND(AVG(e.salary), 2) AS avg_salary
FROM departments d
INNER JOIN employees e ON e.dept_id = d.id
GROUP BY d.dept_name
ORDER BY avg_salary DESC;

Summary

📋 Summary

INNER JOIN — Returns only rows with a matching value in both tables.
Unmatched rows are excluded — Eve (no dept) and Finance (no employees) do not appear.
JOIN = INNER JOIN — The keyword alone defaults to INNER JOIN.
Use aliases — Always prefix column references with table aliases in multi-table queries.
Chain multiple JOINs — Add one INNER JOIN per additional table.
Multi-column joins — Use AND in ON to match composite keys.
ON for join condition, WHERE for row filter — Keeps intent clear, matters for outer joins.

FAQ

Does INNER JOIN order matter (which table is on which side)?+

For INNER JOIN, the result is the same regardless of table order — the join is commutative. The query optimizer may reorder tables anyway based on statistics. Conventionally, you write the "main" table first and the "lookup" table second (e.g. employees INNER JOIN departments). For outer joins (LEFT/RIGHT), order absolutely matters.

What happens if there are duplicate matches?+

If multiple rows in the right table match a single row from the left table, you get multiple result rows (a fan-out). For example: one order with three order items produces three rows when you join orders to order_items. This is expected and correct. Watch for it when aggregating — SUM() on the orders table after joining to items will double-count. Use subqueries or GROUP BY carefully to avoid this.

How do I join on a NULL value?+

You cannot match on NULL with the standard ON condition because NULL = NULL is UNKNOWN, not TRUE. If you need to treat NULLs as equal, use: ON (e.dept_id = d.id OR (e.dept_id IS NULL AND d.id IS NULL)). In practice, NULL foreign keys mean "no relationship" and you would use LEFT JOIN rather than trying to match NULLs.

Can I use INNER JOIN with a subquery?+

Yes — any table reference in a JOIN can be a subquery (also called a derived table): INNER JOIN (SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id) AS dept_avg ON e.dept_id = dept_avg.dept_id. In modern SQL, a CTE (WITH dept_avg AS (...)) is usually more readable for this pattern.