INNER JOIN

Combine rows from multiple tables using INNER JOIN.

20 min read
Beginner

Introduction to INNER JOIN

So far, you've queried data from a single table at a time. But real-world databases store related data across multiple tables. For example:

  • Customers are in one table, their orders in another
  • Products are in one table, order details in another
  • Employees are in one table, their departments in another

How do you combine this data to answer questions like "Show me all orders with customer names" or "List products with their category information"?

That's where JOINs come in! They let you combine rows from two or more tables based on related columns.

Combines rows from two tables where there is a matching value in both tables. Only returns rows that have matches in both tables - unmatched rows are excluded.

Why split data across tables?

  • Avoid duplication (don't repeat customer info for every order)
  • Organize data logically
  • Easier to maintain and update
  • Better performance

Let's work with two related tables:

customers
id
name
city
country
1Alice CorpNew YorkUSA
2Bob IncLondonUK
3Charlie LtdTorontoCanada
4Diana CoSydneyAustralia
orders
id
customer_id
order_date
total
10112024-01-15500.00
10222024-01-20350.00
10312024-02-01750.00
10432024-02-05220.00

Notice how orders.customer_id references customers.id. This is called a foreign key relationship.

Basic INNER JOIN Syntax

Syntax:

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

Parts of a JOIN:

  1. FROM table1 - the first table (left table)
  2. INNER JOIN table2 - the second table (right table)
  3. ON condition - how to match rows (usually table1.id = table2.foreign_key)
sql
-- Join orders with customers to see customer names
SELECT
  orders.id,
  customers.name,
  orders.order_date,
  orders.total
FROM orders
INNER JOIN customers
  ON orders.customer_id = customers.id;

What happened:

  1. For each row in orders, SQL looked at customer_id
  2. Found the matching row in customers where id equals that customer_id
  3. Combined the columns from both tables into one result row

Notice that customer "Diana Co" doesn't appear - she has no orders, so there's no match!

Using Table Aliases with Joins

With joins, table aliases become essential for clarity and brevity:

sql
-- Using table aliases (recommended!)
SELECT
  o.id AS order_id,
  c.name AS customer_name,
  c.city,
  o.order_date,
  o.total
FROM orders AS o
INNER JOIN customers AS c
  ON o.customer_id = c.id;

Much cleaner! o.id instead of orders.id, c.name instead of customers.name.

Disambiguating columns:

Both tables have an id column. Without prefixes (o.id, c.id), SQL wouldn't know which one you mean!

Always use table prefixes when columns exist in both tables:

SELECT
  o.id,    -- orders.id
  c.id,    -- customers.id
  c.name   -- customers.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;

How INNER JOIN Works - Only Matches

INNER JOIN returns ONLY rows where there's a match in BOTH tables.

Let's see what happens with our data:

Orders table:

  • Order 101: customer_id = 1 → Matches customer Alice (id=1) ✓
  • Order 102: customer_id = 2 → Matches customer Bob (id=2) ✓
  • Order 103: customer_id = 1 → Matches customer Alice (id=1) ✓
  • Order 104: customer_id = 3 → Matches customer Charlie (id=3) ✓

Customers table:

  • Customer 4 (Diana) → No orders ❌ Not in result!

INNER JOIN excludes Diana because she has no matching orders.

Visualization:

INNER JOIN returns the intersection (overlap) of both tables:

Table A     Table B
   ┌─────┐ ┌─────┐
   │     │ │     │
   │  ┌──┴─┴──┐  │  ← INNER JOIN returns only this part
   │  │ Match │  │
   │  └──┬─┬──┘  │
   │     │ │     │
   └─────┘ └─────┘

Selecting Columns from Both Tables

You can select any columns from either table after joining:

sql
-- Mix of columns from both tables
SELECT
  c.name AS customer,
  c.country,
  o.id AS order_id,
  o.order_date,
  o.total,
  CONCAT('$', FORMAT(o.total, 2)) AS formatted_total
FROM orders o
INNER JOIN customers c
  ON o.customer_id = c.id;

*Select all columns with :

sql
-- Select all columns from both tables
SELECT *
FROM orders o
INNER JOIN customers c
  ON o.customer_id = c.id
LIMIT 2;

Notice both id columns appear! Use table prefixes to select specific ones.

Filtering Joined Results with WHERE

You can add WHERE clauses to filter the joined results:

sql
-- Only orders from USA customers
SELECT
  c.name,
  c.country,
  o.order_date,
  o.total
FROM orders o
INNER JOIN customers c
  ON o.customer_id = c.id
WHERE c.country = 'USA';
sql
-- Orders over $400 from customers in UK or Canada
SELECT
  c.name,
  c.country,
  o.total
FROM orders o
INNER JOIN customers c
  ON o.customer_id = c.id
WHERE o.total > 400
  AND c.country IN ('UK', 'Canada');

No results because UK customer (Bob) has order of 350 (not > 400), and Canada customer (Charlie) has order of 220.

Joining on Different Column Names

The columns don't need to have the same name - they just need to hold related values:

products
product_id
product_name
category_code
price
1LaptopELEC999
2Desk ChairFURN299
3MouseELEC29
categories
code
category_name
description
ELECElectronicsElectronic devices
FURNFurnitureOffice furniture
BOOKBooksBooks and media
sql
-- Join on products.category_code = categories.code
SELECT
  p.product_name,
  c.category_name,
  p.price
FROM products p
INNER JOIN categories c
  ON p.category_code = c.code;

The category "Books" doesn't appear because no products have category_code = 'BOOK'.

Joining Multiple Tables

You can join more than two tables by chaining INNER JOINs:

order_items
id
order_id
product_id
quantity
price
110112999
21013129
310221299
410311999
sql
-- Join orders, customers, and order_items together
SELECT
  c.name AS customer,
  o.order_date,
  p.product_name,
  oi.quantity,
  oi.price,
  oi.quantity * oi.price AS line_total
FROM orders o
INNER JOIN customers c
  ON o.customer_id = c.id
INNER JOIN order_items oi
  ON oi.order_id = o.id
INNER JOIN products p
  ON oi.product_id = p.product_id;

This joins 4 tables! Each INNER JOIN builds on the previous result.

Join order:

  1. orders JOIN customers (orders + customer names)
  2. Result JOIN order_items (+ product IDs and quantities)
  3. Result JOIN products (+ product names)

INNER JOIN with Aggregates and GROUP BY

Combine JOINs with GROUP BY for powerful reports:

sql
-- Total spending per customer
SELECT
  c.name AS customer,
  c.country,
  COUNT(o.id) AS order_count,
  SUM(o.total) AS total_spent
FROM customers c
INNER JOIN orders o
  ON c.id = o.customer_id
GROUP BY c.id, c.name, c.country
ORDER BY total_spent DESC;

Notice Diana (customer 4) is excluded - she has no orders, so INNER JOIN filters her out.

Category sales report:

sql
-- Total revenue per category
SELECT
  c.category_name,
  COUNT(oi.id) AS items_sold,
  SUM(oi.quantity) AS total_quantity,
  SUM(oi.quantity * oi.price) AS total_revenue
FROM order_items oi
INNER JOIN products p
  ON oi.product_id = p.product_id
INNER JOIN categories c
  ON p.category_code = c.code
GROUP BY c.code, c.category_name
ORDER BY total_revenue DESC;

Common Mistakes to Avoid

Don't make these mistakes:

  1. Forgetting the ON clause: ❌

    • FROM orders INNER JOIN customers (no ON)
    • Results in a Cartesian product (every row × every row)!
    • Always include: ON table1.column = table2.column

  2. Wrong join column: ❌

    • ON orders.id = customers.id (wrong!)
    • Should be: ON orders.customer_id = customers.id
    • Make sure you join on the foreign key relationship

  3. Ambiguous column names: ❌

    • SELECT id FROM orders JOIN customers...
    • Which id - orders.id or customers.id?
    • Always use: o.id or c.id

  4. Using WHERE instead of ON: Works but confusing ❌

    • FROM orders JOIN customers WHERE orders.customer_id = customers.id
    • Use ON for join conditions, WHERE for filtering

  5. Assuming all rows will be included: ❌

    • INNER JOIN excludes non-matching rows!
    • Diana (no orders) doesn't appear in our results
    • Use LEFT JOIN if you need all rows from one table

  6. Not using aliases: Makes queries hard to read ❌

    • orders.customer_id = customers.id (verbose)
    • Better: o.customer_id = c.id

  7. Incorrect GROUP BY with joins: ❌

    • Must include join columns in GROUP BY if selected
    • GROUP BY c.id, c.name (include both if selecting both)

Practical Examples

Example 1: Employee departments

employees
id
name
dept_id
salary
1Alice1095000
2Bob2075000
3Charlie1085000
departments
id
dept_name
location
10EngineeringNew York
20MarketingLondon
30SalesChicago
sql
SELECT
  e.name AS employee,
  d.dept_name AS department,
  d.location,
  e.salary
FROM employees e
INNER JOIN departments d
  ON e.dept_id = d.id
ORDER BY d.dept_name, e.name;

Sales department doesn't appear - no employees assigned to it yet!

Example 2: Product inventory

sql
-- Products with category information
SELECT
  p.product_name,
  c.category_name,
  p.price,
  CASE
    WHEN p.price < 100 THEN 'Budget'
    WHEN p.price < 500 THEN 'Mid-range'
    ELSE 'Premium'
  END AS price_tier
FROM products p
INNER JOIN categories c
  ON p.category_code = c.code
WHERE p.price > 50
ORDER BY p.price DESC;

Key Takeaways

What you learned:

✅ INNER JOIN combines rows from two tables based on matching values ✅ Only returns rows that have matches in BOTH tables ✅ Syntax: FROM table1 INNER JOIN table2 ON table1.column = table2.column ✅ Use table aliases (o, c, p) for clarity ✅ Always use table prefixes for column names (o.id, c.name) ✅ Can join multiple tables by chaining JOINs ✅ Combine with WHERE to filter, GROUP BY to aggregate ✅ ON clause specifies the join condition (how to match rows) ✅ Non-matching rows are excluded from results

Coming up next: We'll learn about LEFT JOIN and RIGHT JOIN to include non-matching rows from one table!

Practice Exercise: Try these queries:

  1. Join orders and customers, show customer name and order total
  2. Join products and categories, filter to Electronics category
  3. Join employees and departments, count employees per department
  4. Join orders, customers, and order_items - calculate total revenue per customer
  5. Find all orders with customer country = 'USA', show customer name and order date
🗄️

SQL Fundamentals

Master SQL from basics to advanced topics. Learn to query, manipulate, and analyze data in relational databases.