SQL Joins
INNER JOIN for Combining Tables
INNER JOIN retrieves only matching rows from two or more related tables.
- Matching Records β returns rows where join condition is met
- Related Data β combines columns from multiple tables Connect your data across tables efficiently.
What is an INNER JOIN?
DfINNER JOIN
An INNER JOIN combines rows from two or more tables based on a related column, returning only the rows where the join condition is satisfied in both tables.
-- Basic INNER JOIN combining customers and orders
SELECT
c.customer_name,
o.order_id,
o.order_date
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;
-- Join three tables together
SELECT
c.customer_name,
o.order_id,
p.product_name,
oi.quantity
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id;
INNER JOIN Syntax
DfJOIN Syntax
The INNER JOIN clause uses the ON keyword to specify the join condition. You can use table aliases for cleaner syntax and qualify column names to avoid ambiguity.
-- Using table aliases
SELECT
c.first_name,
c.last_name,
o.total_amount
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id;
-- Join with multiple conditions
SELECT
e.first_name,
d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
AND e.location = d.location;
INNER JOIN Behavior
| Customers Table | Orders Table | INNER JOIN Result |
|---|---|---|
| ID=1, Name=John | OrderID=101, CustID=1 | ID=1, John, Order 101 |
| ID=2, Name=Jane | OrderID=102, CustID=2 | ID=2, Jane, Order 102 |
| ID=3, Name=Bob | No orders | No row returned |
| ID=4, Name=Alice | OrderID=103, CustID=4 | ID=4, Alice, Order 103 |
-- INNER JOIN only returns matching rows
SELECT
c.customer_id,
c.customer_name,
COUNT(o.order_id) AS order_count
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;
-- Filter on joined data
SELECT
p.product_name,
SUM(oi.quantity) AS total_sold
FROM products p
INNER JOIN order_items oi ON p.product_id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date >= '2024-01-01'
GROUP BY p.product_name;
Use INNER JOIN when you only need records that exist in both tables. It's the most efficient join type for filtering unmatched rows.
Common INNER JOIN Patterns
-- Self-join to find employees and their managers
SELECT
e.first_name AS employee,
m.first_name AS manager
FROM employees e
INNER JOIN employees m ON e.manager_id = m.employee_id;
-- Join with calculated column
SELECT
c.customer_name,
o.order_id,
(oi.quantity * oi.unit_price) AS line_total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id;
-- Join and aggregate
SELECT
d.department_name,
COUNT(e.employee_id) AS employee_count,
AVG(e.salary) AS avg_salary
FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;
INNER JOIN excludes rows that don't have matches in both tables. If you need all rows from one table regardless of matches, use LEFT JOIN instead.
Key Takeaways
- INNER JOIN returns only rows with matching records in both tables
- Use the ON clause to specify the join condition
- INNER JOIN is the default join type if JOIN is used without LEFT/RIGHT
- Combine multiple INNER JOINs to link several tables
- Use LEFT JOIN when you need all rows from the primary table