INNER JOIN

INNER JOIN

2 min read 4 runnable examples Quiz included

Real databases split data across tables to avoid repetition. Orders don't store the customer's name; they store a customer_id that points to the customers table. A JOIN puts them back together.

How the tables connect

Diagram

customers                orders
---------------          -----------------------
customer_id  ◄─────────  customer_id
name                     order_id
city                     order_date, status

Each order stores the id of its customer. A join follows that link to bring the two rows together.

customers.customer_id is the primary key (unique for each customer). orders.customer_id is a foreign key that refers to it.

SQL

SELECT orders.order_id, orders.order_date, customers.name
FROM orders
INNER JOIN customers ON customers.customer_id = orders.customer_id;

For each order, the database finds the customer with the matching id and joins the two rows into one. INNER JOIN keeps only rows that have a match in both tables. Writing just JOIN means the same thing.

Table aliases

Writing table names over and over is tiring, so give them short aliases:

SQL

SELECT o.order_id, c.name, c.country, o.status
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'delivered'
ORDER BY o.order_id;

Joining three tables

Which products did each order contain? order_items links orders to products:

SQL

SELECT o.order_id, c.name AS customer, p.name AS product, oi.quantity
FROM orders o
JOIN customers c    ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p     ON p.product_id = oi.product_id
ORDER BY o.order_id;

Joins with grouping

Joins and GROUP BY together answer real business questions, like "how much has each customer spent?":

SQL

SELECT c.name, ROUND(SUM(oi.quantity * p.price), 2) AS total_spent
FROM customers c
JOIN orders o       ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p     ON p.product_id = oi.product_id
WHERE o.status <> 'cancelled'
GROUP BY c.name
ORDER BY total_spent DESC;

Finished this lesson?Mark it complete to earn +20 XP and move your progress on.