SQL INNER JOIN

INNER JOIN returns the rows that have a match in both tables. It is by far the most used join: orders with their customers, order lines with their products, employees with their departments. This lesson builds inner joins step by step on the sample shop data, from two tables to four.

INNER JOIN syntax

SELECT columnsFROM table1 t1INNER JOIN table2 t2 ON t2.key = t1.key;

INNER is optional: JOIN alone means INNER JOIN.

Sample data

Join two tables

Each order with its customer’s name:

SELECT o.order_id, o.order_date, c.customer_name, c.cityFROM orders oINNER JOIN customers c ON c.customer_id = o.customer_idORDER BY o.order_id;

How the matching works

Think of it as a nested loop: for every row on the left, find every row on the right where the ON condition is true, and output one combined row per match.

The database actually uses indexes and smarter algorithms, but the result is the same.

One-to-many joins multiply rows

An order can have several lines. Joining orders to order_items gives one row per line, not per order:

SELECT o.order_id, o.status, oi.product_id, oi.quantityFROM orders oJOIN order_items oi ON oi.order_id = o.order_idWHERE o.order_id IN (1001, 1002, 1005)ORDER BY o.order_id, oi.product_id;

Join three and four tables

Customer name, product name and line amount for every order line:

SELECT c.customer_name, o.order_id, p.product_name, oi.quantity,       oi.quantity * p.price AS amountFROM orders oJOIN customers   c  ON c.customer_id = o.customer_idJOIN order_items oi ON oi.order_id   = o.order_idJOIN products    p  ON p.product_id  = oi.product_idWHERE c.customer_id = 1ORDER BY o.order_id, p.product_name;

INNER JOIN with GROUP BY

Revenue per customer, highest first:

SELECT c.customer_name,       COUNT(DISTINCT o.order_id) AS orders,       SUM(oi.quantity * p.price) AS revenueFROM customers cJOIN orders o       ON o.customer_id = c.customer_idJOIN order_items oi ON oi.order_id   = o.order_idJOIN products p     ON p.product_id  = oi.product_idWHERE o.status <> 'Cancelled'GROUP BY c.customer_id, c.customer_nameORDER BY revenue DESC;

Conditions in ON vs WHERE

For an inner join, a filter gives the same result in ON or in WHERE:

SELECT o.order_id, c.customer_nameFROM orders oJOIN customers c ON c.customer_id = o.customer_id AND c.city = 'Delhi';

Keep join conditions in ON and filters in WHERE for readability. For outer joins the placement changes the result; see LEFT JOIN.

USING

When both columns have the same name, USING (column) is a shortcut for ON a.column = b.column, and the shared column appears only once in SELECT *:

SELECT order_id, customer_id, customer_nameFROM ordersJOIN customers USING (customer_id)WHERE order_id < 1004;

Common mistakes

• Forgetting the ON condition (or writing FROM a, b with no WHERE) produces a cross join: every row times every row.
• Joining on the wrong column, for example ON c.customer_id = o.order_id, runs without an error and returns nonsense. Always check the row count.
• Ambiguous column names: if both tables have dept_id, write e.dept_id or d.dept_id.

SELECT p.product_name, oi.quantityFROM order_items oiJOIN products p ON p.product_id = oi.product_idWHERE oi.order_id = 1008;

SELECT e.first_name, d.dept_name, d.locationFROM employees eJOIN departments d ON d.dept_id = e.dept_idWHERE d.location = 'Mumbai';

INNER JOIN FAQ