Chapter 18 of 35

INNER JOIN

The INNER JOIN is the join you will write most often. Whenever you show an order together with its customer, an employee with their department, or a product with its category, you are almost certainly writing an inner join.

In the SQL Joins chapter we saw the big picture: a join pairs up rows from two tables using a join condition. In this chapter we go deep into the inner join: exactly which rows it returns, how to trace it by hand, filtering and grouping joined data, joining three tables, multi-column conditions, and the "row multiplication" trap that produces wrong totals in real reports.

All examples use the shared company_db sample database, and every output shown is real MySQL 8.4 output.


What is an INNER JOIN?

An INNER JOIN returns only those combinations of rows from the two tables for which the join condition is TRUE. A row that has no matching partner in the other table does not appear in the result.

SELECT columns
FROM table1 t1
INNER JOIN table2 t2
    ON t1.column = t2.column;

The keyword INNER is optional: a plain JOIN means INNER JOIN. Both spellings are used in practice; many teams write JOIN for inner joins and spell out LEFT JOIN for outer ones.

Let's list every order with the name of the customer who placed it:

SELECT o.order_id, o.customer_id, c.name AS customer, o.amount, o.status
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
ORDER BY o.order_id;
+----------+-------------+--------------+---------+-----------+
| order_id | customer_id | customer     | amount  | status    |
+----------+-------------+--------------+---------+-----------+
|     1001 |         101 | Rahul Verma  | 2500.00 | Delivered |
|     1002 |         102 | Kavya Menon  | 4800.00 | Delivered |
|     1003 |         101 | Rahul Verma  | 1200.00 | Cancelled |
|     1004 |         103 | Aditya Joshi | 7300.00 | Delivered |
|     1005 |         104 | Neha Agarwal |  950.00 | Shipped   |
|     1006 |         102 | Kavya Menon  | 3100.00 | Delivered |
|     1007 |         101 | Rahul Verma  | 5600.00 | Pending   |
+----------+-------------+--------------+---------+-----------+
7 rows in set (0.00 sec)

The orders table has 8 rows and customers has 5. The result has 7 rows. Let's find out exactly why.


Tracing an INNER JOIN by Hand

Think of the join as: for every order, look for customers whose customer_id equals the order's customer_id, and output one row per match.

order   o.customer_id   matching customer(s)        output rows
-----   -------------   -------------------------   -----------
1001    101             101 Rahul Verma             1
1002    102             102 Kavya Menon             1
1003    101             101 Rahul Verma             1
1004    103             103 Aditya Joshi            1
1005    104             104 Neha Agarwal            1
1006    102             102 Kavya Menon             1
1007    101             101 Rahul Verma             1
1008    NULL            none (NULL = 101? UNKNOWN)  0   <- guest order dropped
                                                    ---
                                                    7

Customer 105 Siddharth Rao: no order points to him  -> never appears

So an inner join drops unmatched rows from both sides:

  • Order 1008 has customer_id = NULL. A comparison with NULL is UNKNOWN, never TRUE, so it matches nobody. (Even two NULLs do not match each other in a join.)

  • Customer 105 (Siddharth) has no orders, so there is nothing to pair him with.

This is the defining property of an inner join, and the most common source of "missing rows" surprises. If the question is "list all customers and their orders, including customers who never ordered", an inner join is the wrong tool; that is the job of the LEFT JOIN, covered in the next chapter.

How Many Rows Can an Inner Join Return?

A row on one side can match zero, one, or many rows on the other side:

  • Zero matches: the row disappears.

  • One match: one output row.

  • Many matches: one output row per match. Rahul Verma appears 3 times above because he has 3 orders.

So the result can be smaller or larger than either table. For two tables of sizes m and n, an inner join returns anywhere from 0 up to m × n rows (the upper limit is reached only when every row matches every row).


INNER JOIN with WHERE

After the join builds the combined rows, WHERE filters them just like it filters a single table. You can filter on columns from either table. Engineers earning more than 90,000:

SELECT e.name, d.dept_name, e.salary
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = 'Engineering'
  AND e.salary > 90000
ORDER BY e.salary DESC, e.name;
+--------------+-------------+-----------+
| name         | dept_name   | salary    |
+--------------+-------------+-----------+
| Aarav Sharma | Engineering | 150000.00 |
| Priya Iyer   | Engineering |  95000.00 |
| Rohan Mehta  | Engineering |  95000.00 |
+--------------+-------------+-----------+
3 rows in set (0.00 sec)

ON or WHERE? For Inner Joins, Both Give the Same Result

For an inner join you could put an extra filter in the ON clause instead of WHERE:

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d
    ON e.dept_id = d.dept_id AND d.location = 'Delhi';
+-------------+-----------+
| name        | dept_name |
+-------------+-----------+
| Karan Patel | HR        |
| Meera Nair  | HR        |
+-------------+-----------+
2 rows in set (0.00 sec)
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
WHERE d.location = 'Delhi';
+-------------+-----------+
| name        | dept_name |
+-------------+-----------+
| Karan Patel | HR        |
| Meera Nair  | HR        |
+-------------+-----------+
2 rows in set (0.00 sec)

Same result. An inner join keeps a pair only if every condition is TRUE, so it does not matter whether a condition is written in ON or WHERE; the optimizer treats them the same. The convention is still worth following: ON describes how the tables relate; WHERE describes which rows you want. This becomes more than style for outer joins, where moving a condition between ON and WHERE changes the result (shown in the LEFT, RIGHT & FULL OUTER JOIN chapter).

Table Order Does Not Matter

employees JOIN departments and departments JOIN employees produce the same rows (only the column order in SELECT * would differ):

SELECT e.name, d.dept_name
FROM departments d
JOIN employees e ON e.dept_id = d.dept_id
WHERE d.location = 'Delhi';
+-------------+-----------+
| name        | dept_name |
+-------------+-----------+
| Karan Patel | HR        |
| Meera Nair  | HR        |
+-------------+-----------+
2 rows in set (0.00 sec)

Inner join is commutative and associative, which is why the MySQL optimizer is free to choose the order in which it reads the tables.


Selecting Columns from a Join

SELECT * on a join returns all columns of both tables, including both copies of the join column:

SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_id = 1004;
+----------+-------------+------------+---------+-----------+-------------+--------------+------+-------------+
| order_id | customer_id | order_date | amount  | status    | customer_id | name         | city | signup_date |
+----------+-------------+------------+---------+-----------+-------------+--------------+------+-------------+
|     1004 |         103 | 2024-02-14 | 7300.00 | Delivered |         103 | Aditya Joshi | Pune | 2023-03-22  |
+----------+-------------+------------+---------+-----------+-------------+--------------+------+-------------+
1 row in set (0.00 sec)

Two columns are named customer_id, which is confusing for application code that reads columns by name. You can use alias.* to take all columns of one table plus selected columns of the other:

SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_id = 1004;
+----------+-------------+------------+---------+-----------+--------------+
| order_id | customer_id | order_date | amount  | status    | name         |
+----------+-------------+------------+---------+-----------+--------------+
|     1004 |         103 | 2024-02-14 | 7300.00 | Delivered | Aditya Joshi |
+----------+-------------+------------+---------+-----------+--------------+
1 row in set (0.00 sec)

In production code, list the columns you need explicitly, and give clashing names an alias (e.name AS employee, c.name AS customer).


INNER JOIN with GROUP BY and HAVING

This is where joins become really useful for reports. Customer-wise spending (excluding cancelled orders), with names instead of IDs:

SELECT c.name,
       COUNT(*)      AS num_orders,
       SUM(o.amount) AS total_spent
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status <> 'Cancelled'
GROUP BY c.customer_id
ORDER BY total_spent DESC;
+--------------+------------+-------------+
| name         | num_orders | total_spent |
+--------------+------------+-------------+
| Rahul Verma  |          2 |     8100.00 |
| Kavya Menon  |          2 |     7900.00 |
| Aditya Joshi |          1 |     7300.00 |
| Neha Agarwal |          1 |      950.00 |
+--------------+------------+-------------+
4 rows in set (0.00 sec)

Order of work: the join builds 7 order-customer rows, WHERE removes the cancelled order 1003, GROUP BY makes one bucket per customer, and the aggregates are computed per bucket. We group by c.customer_id (the primary key) rather than c.name, because two different customers could share a name; selecting c.name is still allowed because it is functionally dependent on the key.

Departments whose average salary is above 75,000:

SELECT d.dept_name,
       COUNT(*) AS headcount,
       ROUND(AVG(e.salary), 2) AS avg_salary
FROM departments d
INNER JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_id
HAVING AVG(e.salary) > 75000
ORDER BY avg_salary DESC;
+-------------+-----------+------------+
| dept_name   | headcount | avg_salary |
+-------------+-----------+------------+
| Engineering |         3 |  113333.33 |
| Marketing   |         1 |   82000.00 |
| Sales       |         3 |   76666.67 |
+-------------+-----------+------------+
3 rows in set (0.00 sec)

Research (no employees) cannot appear in any inner-join report about employees, whatever the HAVING condition. Showing it with a count of 0 needs a LEFT JOIN.


The Row Multiplication Trap

Remember: a row that matches many rows is repeated once per match. In a one-to-many join, the "one" side gets duplicated:

SELECT d.dept_name, d.location, e.name
FROM departments d
INNER JOIN employees e ON e.dept_id = d.dept_id
WHERE d.dept_id = 10;
+-------------+-----------+--------------+
| dept_name   | location  | name         |
+-------------+-----------+--------------+
| Engineering | Bengaluru | Aarav Sharma |
| Engineering | Bengaluru | Priya Iyer   |
| Engineering | Bengaluru | Rohan Mehta  |
+-------------+-----------+--------------+
3 rows in set (0.00 sec)

That is expected when you display the rows. The danger is when you aggregate after the join and forget about the duplication. Suppose the question is "how many customers who have placed orders live in each city?":

SELECT c.city, COUNT(*) AS customers_wrong
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.city
ORDER BY c.city;
+-----------+-----------------+
| city      | customers_wrong |
+-----------+-----------------+
| Bengaluru |               2 |
| Delhi     |               3 |
| Mumbai    |               1 |
| Pune      |               1 |
+-----------+-----------------+
4 rows in set (0.00 sec)

Delhi shows 3, but only one customer (Rahul) lives in Delhi. COUNT(*) counted joined rows, i.e. Rahul's 3 orders. The fix is to count distinct customers:

SELECT c.city, COUNT(DISTINCT c.customer_id) AS customers
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.city
ORDER BY c.city;
+-----------+-----------+
| city      | customers |
+-----------+-----------+
| Bengaluru |         1 |
| Delhi     |         1 |
| Mumbai    |         1 |
| Pune      |         1 |
+-----------+-----------+
4 rows in set (0.00 sec)

The same trap hits SUM: if you join a parent to its children and then SUM a column of the parent, that value is added once per child. Before aggregating a joined result, always ask: "what does one row of the joined result represent?" Here, one row = one order, not one customer.


Joining Three or More Tables

Joins chain naturally. Each new JOIN ... ON attaches another table to the rows built so far:

orders  --(o.customer_id = c.customer_id)-->  customers  --(c.city = d.location)-->  departments

Suppose the company wants to know, for every order, which of its offices is in the customer's city (for local delivery support). The office city is departments.location:

SELECT o.order_id, c.name AS customer, c.city, d.dept_name AS local_office
FROM orders o
INNER JOIN customers c   ON o.customer_id = c.customer_id
INNER JOIN departments d ON d.location = c.city
ORDER BY o.order_id;
+----------+--------------+-----------+--------------+
| order_id | customer     | city      | local_office |
+----------+--------------+-----------+--------------+
|     1001 | Rahul Verma  | Delhi     | HR           |
|     1002 | Kavya Menon  | Bengaluru | Engineering  |
|     1003 | Rahul Verma  | Delhi     | HR           |
|     1004 | Aditya Joshi | Pune      | Marketing    |
|     1005 | Neha Agarwal | Mumbai    | Sales        |
|     1006 | Kavya Menon  | Bengaluru | Engineering  |
|     1007 | Rahul Verma  | Delhi     | HR           |
+----------+--------------+-----------+--------------+
7 rows in set (0.00 sec)

Step by step: the first join produces the 7 order-customer rows we traced earlier. The second join attaches to each of those the department whose location equals the customer's city. Every customer city here has exactly one office, so the count stays 7. If a city had no office, those orders would be dropped (inner join); if a city had two offices, those orders would appear twice.

This example also shows that a join does not have to follow a foreign key. c.city = d.location is a perfectly legal join condition between two ordinary columns.

A rule of thumb: joining N tables needs at least N − 1 join conditions. Fewer than that, and some pair of tables is cross-joined.


Join Conditions with More Than One Column

The ON clause can contain several conditions combined with AND. Which employees live in the same city as their department's office?

SELECT e.name, e.city, d.dept_name, d.location
FROM employees e
INNER JOIN departments d
    ON e.dept_id = d.dept_id
   AND e.city = d.location
ORDER BY e.emp_id;
+--------------+-----------+-------------+-----------+
| name         | city      | dept_name   | location  |
+--------------+-----------+-------------+-----------+
| Aarav Sharma | Bengaluru | Engineering | Bengaluru |
| Rohan Mehta  | Bengaluru | Engineering | Bengaluru |
| Sneha Kapoor | Mumbai    | Sales       | Mumbai    |
| Vikram Singh | Mumbai    | Sales       | Mumbai    |
| Karan Patel  | Delhi     | HR          | Delhi     |
+--------------+-----------+-------------+-----------+
5 rows in set (0.00 sec)

Priya (Engineering, lives in Chennai), Ananya (Sales, Kolkata), Meera (HR, Kochi) and Arjun (Marketing, lives in Hyderabad but the office is in Pune) fail the second condition. Multi-column join conditions are also how you join tables that have a composite key, for example ON a.order_id = b.order_id AND a.line_no = b.line_no.

Joining on Non-Key Columns

Joining on a column that is not unique on either side can create many-to-many matches. Employees and customers who live in the same city:

SELECT e.name AS employee, c.name AS customer, e.city
FROM employees e
INNER JOIN customers c ON e.city = c.city
ORDER BY e.city, e.name, c.name;
+--------------+--------------+-----------+
| employee     | customer     | city      |
+--------------+--------------+-----------+
| Aarav Sharma | Kavya Menon  | Bengaluru |
| Rohan Mehta  | Kavya Menon  | Bengaluru |
| Karan Patel  | Rahul Verma  | Delhi     |
| Sneha Kapoor | Neha Agarwal | Mumbai    |
| Vikram Singh | Neha Agarwal | Mumbai    |
+--------------+--------------+-----------+
5 rows in set (0.00 sec)

Ishita (city NULL) and Siddharth (city NULL) do not match each other, because NULL = NULL is not TRUE. If you ever really need NULLs to match, MySQL's null-safe operator <=> can be used in the ON clause, but that is rarely what the business wants.


A Wrong Join Condition Fails Silently

SQL cannot know which columns are supposed to match. If you compare the wrong columns, you get a wrong answer, not an error:

SELECT o.order_id, c.name, o.amount
FROM orders o
JOIN customers c ON c.customer_id = o.order_id;
Empty set (0.00 sec)

Customer IDs (101 to 105) never equal order IDs (1001 to 1008), so nothing matches. With other data such a mistake might return plausible-looking rows, which is worse. Always join a foreign key to the key it references, and sanity-check the row count: joining 8 orders to their customers should give at most 8 rows.


Performance Notes

  • Index the join columns. Primary keys are indexed automatically, and InnoDB creates an index on foreign key columns, so FK-to-PK joins are fast by default. Joins on other columns (like city) may need an index you create yourself (see the Indexes chapter).

  • Join columns should have the same data type (and the same character set and collation for strings). Comparing an INT with a VARCHAR forces type conversion on every row and can stop MySQL from using the index.

  • Filter early. Conditions in WHERE on indexed columns reduce the number of rows that ever have to be joined.

  • Select only the columns you need; SELECT * over several joined tables moves a lot of unnecessary data.


Common Mistakes

  • Expecting rows without a match. Inner join drops customers without orders, orders without customers and anything with a NULL join column.

  • Counting joined rows instead of entities. Use COUNT(DISTINCT key) after a one-to-many join.

  • Missing a join condition when chaining many tables, producing a partial Cartesian product.

  • Ambiguous columns such as name or customer_id that exist in both tables. Qualify them.

  • Grouping by a non-unique name (GROUP BY c.name) when two people can share a name; group by the key.

  • Comparing the wrong columns in ON; the query runs anyway.


Interview Questions

What does an INNER JOIN return?

Only the row combinations for which the join condition is TRUE. Rows with no match in the other table, and rows whose join column is NULL, are excluded.

Is JOIN the same as INNER JOIN?

Yes. INNER is optional; JOIN alone means inner join.

Can an INNER JOIN return more rows than either table?

Yes. If a row matches several rows on the other side, it is repeated once per match. The maximum is m × n rows.

Does it matter whether a filter is in ON or WHERE in an inner join?

Not for the result: both must be TRUE for a row to survive. By convention, ON holds the relationship between tables and WHERE holds filters. For outer joins the placement does change the result.

Do NULL values match in an inner join?

No. NULL = NULL evaluates to UNKNOWN, so rows with NULL in the join column never match (unless you use the null-safe <=> operator).

How do you join three tables?

Chain the joins: FROM a JOIN b ON ... JOIN c ON .... N tables need at least N − 1 join conditions.

Why does COUNT(*) give a wrong number after a join?

Because a one-to-many join repeats the "one" side for every matching child row, so COUNT(*) counts joined rows. Use COUNT(DISTINCT parent_key) or aggregate before joining.

Does the order of tables in an INNER JOIN matter?

Not for the result. Inner joins are commutative and associative; the optimizer chooses the physical order.


Summary

  • INNER JOIN (or just JOIN) returns only matched row pairs.

  • Unmatched rows from both sides are dropped, including rows with NULL join columns.

  • One row can match many rows; it is then repeated, which can inflate counts and sums.

  • WHERE, GROUP BY, HAVING and ORDER BY work on the joined rows as on a single table.

  • For inner joins, ON and WHERE filters give the same result; keep relationships in ON and filters in WHERE.

  • Chain joins for 3+ tables; use AND in ON for multi-column conditions.

  • Index join columns and keep their data types identical.

Inner joins quietly drop data that has no partner, such as Siddharth (no orders) and the Research department (no employees). In the next chapter, LEFT, RIGHT & FULL OUTER JOIN, we learn how to keep those rows.