SQL INNER JOIN Explained Simply (With Diagrams and Examples)

Real databases split information across several tables. Customers live in one table, their orders in another. To answer a question like "Which customer placed each order?" you need to bring those tables together. That is what a JOIN does, and INNER JOIN is the most common one.

An INNER JOIN returns only the rows that have a match in both tables.

Why data is split into tables#

Imagine storing the customer's name and city on every single order. If Asha places 50 orders and then moves city, you would have to update 50 rows, and missing one would leave your data contradicting itself. Instead, databases store each customer once and let orders point to the customer with an id. Joins put the pieces back together when you need them.

The example tables#

customers

orders

The customer_id column in orders points to a row in customers. A column that refers to another table like this is called a foreign key.

Notice two things that make this example realistic:

• Meera and John have not placed any orders.
• Order 104 belongs to customer_id 5, which does not exist in customers (perhaps that customer was deleted).

INNER JOIN syntax#

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

• FROM table_a INNER JOIN table_b names the two tables.
• ON describes how rows match, usually by an id that appears in both tables.

Your first INNER JOIN#

SELECT customers.name, orders.order_id, orders.amount
FROM customers
INNER JOIN orders
  ON customers.customer_id = orders.customer_id
ORDER BY orders.order_id;

name | order_id | amount
-----+----------+-------
Asha | 101      | 250
Asha | 102      | 120
Ravi | 103      | 560

How the database builds the result#

For every customer, the database looks for orders with the same customer_id:

Three important lessons hide in this table:

1. A row that matches several rows appears several times (Asha appears twice).
2. Rows without a match on either side are dropped (Meera, John, and order 104).
3. The order of the two tables does not change the result of an INNER JOIN. orders INNER JOIN customers returns the same rows.

Use table aliases to keep queries short#

Writing customers. and orders. everywhere gets tiring. Give each table a short alias right after its name:

SELECT c.name, o.order_id, o.amount
FROM customers AS c
INNER JOIN orders AS o
  ON c.customer_id = o.customer_id;

c now means customers and o means orders. The AS keyword is optional for tables, so FROM customers c works too (and in Oracle you must leave AS out).

Join, then filter with WHERE#

WHERE works on the joined result, so you can filter on columns from either table:

SELECT c.name, c.city, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.amount > 200;

name | city   | order_id | amount
-----+--------+----------+-------
Asha | Mumbai | 101      | 250
Ravi | Delhi  | 103      | 560

Join, then summarise with GROUP BY#

Joins and GROUP BY are a powerful pair. Total spend per customer:

SELECT c.name,
       COUNT(o.order_id) AS orders,
       SUM(o.amount)     AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name
ORDER BY total_spent DESC;

name | orders | total_spent
-----+--------+------------
Ravi | 1      | 560
Asha | 2      | 370

Meera and John are missing because they have no orders, so the INNER JOIN removed them before grouping. If your report must list every customer, including those with zero orders, you need a LEFT JOIN.

Joining more than two tables#

You can chain joins. Suppose a cities table stores the state for each city:

SELECT c.name, ci.state, o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN cities ci ON c.city = ci.city;

Each additional JOIN adds one more table, matched by its own ON condition. A row only survives if it finds a match in every inner-joined table.

Common mistakes#

Practice#

SELECT c.city, o.amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.amount >= 120
ORDER BY o.amount DESC;

city   | amount
-------+-------
Delhi  | 560
Mumbai | 250
Mumbai | 120

Key takeaway#