SQL INNER JOIN: Syntax, Examples & Practice

In the Playground database, orders stores who placed each order as a number, user_id. The customer's name lives in users. An INNER JOIN puts the two together by matching orders.user_id with users.id:

SELECT o.id AS order_id, o.order_date,
       u.first_name, u.last_name, o.total
FROM orders o
INNER JOIN users u
  ON u.id = o.user_id
ORDER BY o.id
LIMIT 5;

▶ Run this query in the SQL Playground

Each result row is built from two tables: the order columns come from orders and the names from users. The ON clause says which rows belong together. o and u are table aliases, short names that show which table each column comes from.

"Inner" means only matches are returned. A customer with no orders does not appear, and neither would an order whose customer did not exist. To keep the unmatched rows, use a LEFT JOIN.

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

INNER JOIN vs JOIN vs a comma in FROM

There are three ways to write the same inner join. All three return the same rows:

SELECT 'INNER JOIN … ON' AS syntax, COUNT(*) AS rows_returned
FROM orders o INNER JOIN users u ON u.id = o.user_id
UNION ALL
SELECT 'JOIN … ON', COUNT(*)
FROM orders o JOIN users u ON u.id = o.user_id
UNION ALL
SELECT 'FROM a, b WHERE', COUNT(*)
FROM orders o, users u WHERE u.id = o.user_id;

▶ Run this query in the SQL Playground

For each row of one table, the database finds the rows of the other table for which ON is true and outputs one combined row per pair:

Joining customers to orders shows all three cases. Look at who is missing and who repeats:

SELECT u.id, u.first_name, o.id AS order_id, o.status
FROM users u
INNER JOIN orders o
  ON o.user_id = u.id
WHERE u.id <= 5
ORDER BY u.id, o.id;

▶ Run this query in the SQL Playground

Customer 3 has no orders and is absent. Customer 4 has three orders and appears three times. Across the whole tables:

SELECT (SELECT COUNT(*) FROM users)  AS customers,
       (SELECT COUNT(*) FROM orders) AS orders,
       COUNT(*)             AS joined_rows,
       COUNT(DISTINCT u.id) AS customers_in_result
FROM users u
INNER JOIN orders o
  ON o.user_id = u.id;

▶ Run this query in the SQL Playground

Every one of the 200 orders finds its customer, so the join returns 200 rows. Only 114 of the 150 customers are in it; the rest have never ordered.

Add one INNER JOIN … ON for each extra table. Every join needs its own condition linking the new table to one already in the query. This lists the items of order 1 with the customer and the product name, using four tables:

SELECT oi.id AS item_id, u.first_name AS customer,
       o.order_date, p.name AS product,
       oi.quantity, oi.subtotal
FROM order_items oi
INNER JOIN orders o
  ON o.id = oi.order_id
INNER JOIN users u
  ON u.id = o.user_id
INNER JOIN products p
  ON p.id = oi.product_id
WHERE o.id = 1
ORDER BY oi.id;

▶ Run this query in the SQL Playground

A join produces a normal set of rows, so everything else works on top of it: WHERE filters the joined rows, GROUP BY groups them and aggregate functions summarise them. Revenue per product category from delivered orders:

SELECT p.category,
       COUNT(DISTINCT o.id) AS orders,
       SUM(oi.quantity)     AS units,
       ROUND(SUM(oi.subtotal), 2) AS revenue
FROM order_items oi
INNER JOIN orders o
  ON o.id = oi.order_id
INNER JOIN products p
  ON p.id = oi.product_id
WHERE o.status = 'delivered'
GROUP BY p.category
ORDER BY revenue DESC;

▶ Run this query in the SQL Playground

The category comes from products, the status from orders and the amounts from order_items. No single table could answer the question. Adding HAVING filters the groups, here customers with at least four orders:

SELECT u.id, u.first_name, u.last_name,
       COUNT(*) AS orders,
       ROUND(SUM(o.total), 2) AS total_spent
FROM users u
INNER JOIN orders o
  ON o.user_id = u.id
GROUP BY u.id, u.first_name, u.last_name
HAVING COUNT(*) >= 4
ORDER BY total_spent DESC;

▶ Run this query in the SQL Playground

ON or WHERE for a filter?

With an INNER JOIN it makes no difference to the result. A condition can sit in either clause:

SELECT 'condition in ON' AS version, COUNT(*) AS rows_returned
FROM users u
INNER JOIN orders o ON o.user_id = u.id AND o.status = 'delivered'
UNION ALL
SELECT 'condition in WHERE', COUNT(*)
FROM users u
INNER JOIN orders o ON o.user_id = u.id
WHERE o.status = 'delivered';

▶ Run this query in the SQL Playground

The convention is to keep the matching rule in ON and filters in WHERE, which is easier to read. The placement only changes the result for outer joins; see ON vs WHERE in a LEFT JOIN.

ON accepts any condition, including several joined with AND. This finds employees who live in the city where their department is based: the department must match and the two cities must be equal.

SELECT e.first_name, e.last_name, e.title,
       d.name AS department, d.location
FROM employees e
INNER JOIN departments d
  ON d.id = e.department_id
 AND d.location = e.city
ORDER BY e.id;

▶ Run this query in the SQL Playground

9 employees qualify. Two other forms are worth recognising:

-- USING: shorthand when the column has the same name in both tables
SELECT *
FROM order_items
INNER JOIN payments USING (order_id);

-- a join does not have to use =
SELECT e.first_name, b.band
FROM employees e
INNER JOIN salary_bands b
  ON e.salary BETWEEN b.min_salary AND b.max_salary;

USING (column) is supported by MySQL, PostgreSQL, Oracle and SQLite, but not SQL Server. NATURAL JOIN goes further and joins on every column name the tables share; avoid it, because adding a column later can silently change the join.

SELECT 'INNER JOIN' AS join_type, COUNT(*) AS rows_returned,
       COUNT(DISTINCT u.id) AS customers
FROM users u
INNER JOIN orders o ON o.user_id = u.id
UNION ALL
SELECT 'LEFT JOIN', COUNT(*), COUNT(DISTINCT u.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

▶ Run this query in the SQL Playground

The INNER JOIN returns 114 customers; the LEFT JOIN returns all 150, adding one NULL row for each customer without orders.

Choose INNER JOIN when a row without a match is of no interest, and LEFT JOIN when the first table must stay complete. Counting "customers" from an inner join to orders really counts customers who have ordered. The SQL JOINs overview compares every join type.

Two things surprise people: rows that vanish, and totals that come out wrong. Both show up when orders are joined to payments. Some orders have no payment; others have several.

SELECT 'orders alone' AS source,
       COUNT(*) AS row_count, ROUND(SUM(total), 2) AS order_total
FROM orders
UNION ALL
SELECT 'orders joined to payments',
       COUNT(*), ROUND(SUM(o.total), 2)
FROM orders o
INNER JOIN payments p
  ON p.order_id = o.id;

▶ Run this query in the SQL Playground

The row count happens to be the same, but the total is different. Orders with no payment were dropped, and orders with two or more payments were counted once per payment. Neither the count nor the sum describes the orders any more.

NULL never matches

A join column that is NULL equals nothing, so the row cannot match. Ten employees have no manager (manager_id is NULL) and disappear from an inner join of the table to itself:

SELECT (SELECT COUNT(*) FROM employees) AS employees,
       COUNT(*) AS rows_from_inner_join
FROM employees e
INNER JOIN employees m
  ON m.id = e.manager_id;

▶ Run this query in the SQL Playground

Joining a table to itself like this is a self join; the two aliases make it behave as two tables.

In BI tools the same join is the default "Inner" option when two tables are merged or related.

Use EXPLAIN (EXPLAIN QUERY PLAN in SQLite) to see whether an index is used. On the small Playground tables every join is instant.

8 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy List each employee's first name, last name and title with the name and location of their department. Show the first ten by employee id. Table: employees, departments

   Show solution

   SQLite · runs in the Playground

   SELECT e.first_name, e.last_name, e.title,
          d.name AS department, d.location
   FROM employees e
   INNER JOIN departments d
     ON d.id = e.department_id
   ORDER BY e.id
   LIMIT 10;

   ▶ Run this query in the SQL Playground

   employees.department_id is the foreign key and departments.id the primary key it points to.

2. 2Easy Show the five most helpful reviews: product name, rating and helpful votes. Table: reviews, products

   Show solution

   SQLite · runs in the Playground

   SELECT p.name AS product, r.rating, r.helpful_votes
   FROM reviews r
   INNER JOIN products p
     ON p.id = r.product_id
   ORDER BY r.helpful_votes DESC, r.id
   LIMIT 5;

   ▶ Run this query in the SQL Playground

   Each review is matched to its product; sorting and LIMIT are applied to the joined rows.

3. 3Medium List the orders placed by customers in Canada during 2024: order id, date, customer name and total, newest first. Table: orders, users

   Show solution

   SQLite · runs in the Playground

   SELECT o.id, o.order_date,
          u.first_name, u.last_name, o.total
   FROM orders o
   INNER JOIN users u
     ON u.id = o.user_id
   WHERE u.country = 'Canada'
     AND o.order_date >= '2024-01-01'
     AND o.order_date <  '2025-01-01'
   ORDER BY o.order_date DESC, o.id;

   ▶ Run this query in the SQL Playground

   WHERE can filter on columns from either table once they are joined. 7 orders match.

4. 4Medium For each department, show the number of employees and the average salary, highest average first. Table: departments, employees

   Show solution

   SQLite · runs in the Playground

   SELECT d.name AS department,
          COUNT(*) AS employees,
          ROUND(AVG(e.salary)) AS avg_salary
   FROM departments d
   INNER JOIN employees e
     ON e.department_id = d.id
   GROUP BY d.id, d.name
   ORDER BY avg_salary DESC;

   ▶ Run this query in the SQL Playground

   Every department has employees, so the inner join returns all 10. If one were empty it would be missing, and a LEFT JOIN would be needed.

5. 5Medium Find the ten best-selling products by units sold. Show the product id, name and total units. Table: order_items, products

   Show solution

   SQLite · runs in the Playground

   SELECT p.id, p.name,
          SUM(oi.quantity) AS units_sold
   FROM order_items oi
   INNER JOIN products p
     ON p.id = oi.product_id
   GROUP BY p.id, p.name
   ORDER BY units_sold DESC, p.id
   LIMIT 10;

   ▶ Run this query in the SQL Playground

   Grouping by the product id as well as the name keeps products that share a name apart.

6. 6Medium List each employee with their manager's name and title, for the first ten employees who have a manager. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT e.first_name AS employee, e.title,
          m.first_name AS manager, m.title AS manager_title
   FROM employees e
   INNER JOIN employees m
     ON m.id = e.manager_id
   ORDER BY e.id
   LIMIT 10;

   ▶ Run this query in the SQL Playground

   A self join: the same table under two aliases. Employees with no manager are left out because their manager_id is NULL.

7. 7Challenging Show total revenue per customer country and product category for delivered orders. Return the ten largest combinations. Table: order_items, orders, users, products

   Show solution

   SQLite · runs in the Playground

   SELECT u.country, p.category,
          ROUND(SUM(oi.subtotal), 2) AS revenue
   FROM order_items oi
   INNER JOIN orders o
     ON o.id = oi.order_id
   INNER JOIN users u
     ON u.id = o.user_id
   INNER JOIN products p
     ON p.id = oi.product_id
   WHERE o.status = 'delivered'
   GROUP BY u.country, p.category
   ORDER BY revenue DESC
   LIMIT 10;

   ▶ Run this query in the SQL Playground

   Four tables, three join conditions. The amounts are summed from order_items, the "many" table, so nothing is double counted.

8. 8Challenging Find reviews written by a customer who actually ordered that product. Show the review id, user id, product id and rating, each review once. Table: reviews, orders, order_items

   Show solution

   SQLite · runs in the Playground

   SELECT DISTINCT r.id AS review_id, r.user_id, r.product_id, r.rating
   FROM reviews r
   INNER JOIN orders o
     ON o.user_id = r.user_id
   INNER JOIN order_items oi
     ON oi.order_id = o.id
    AND oi.product_id = r.product_id
   ORDER BY r.id;

   ▶ Run this query in the SQL Playground

   The second join has two conditions: the item must be in one of the reviewer's orders and be the reviewed product. Only 5 reviews pass.