Home / SQL / INNER JOIN

INNER JOIN

Overview

The INNER JOIN returns rows that have matching values in both tables. It is the most common type of JOIN and is used when you only want records that exist in both tables.

INNER JOIN is the default JOIN type in many databases, so when you write JOIN without specifying INNER or OUTER, it's typically an INNER JOIN.

Basic Syntax

INNER JOIN Syntax

SELECT column1, column2, ...
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Visual Representation

INNER JOIN Diagram

Table A          Table B
┌─────┐         ┌─────┐
│  1  │         │  3  │
│  2  │─────────│  2  │  Returns: 2 (matches only)
│  3  │         │  4  │
│  4  │         │  5  │
└─────┘         └─────┘

Result: Only rows where values match in both tables

Examples

Example 1: Basic INNER JOIN

Customers and their orders

SELECT 
    c.name AS customer_name,
    o.order_date,
    o.total_amount
FROM Customers c
INNER JOIN Orders o 
ON c.customer_id = o.customer_id;

Returns: Only customers who have placed orders. Customers without orders are excluded.

Example 2: Multiple INNER JOINs

Three table JOIN

SELECT 
    c.name AS customer,
    p.product_name AS product,
    oi.quantity,
    oi.unit_price
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id
INNER JOIN OrderItems oi ON o.order_id = oi.order_id
INNER JOIN Products p ON oi.product_id = p.product_id;

Example 3: INNER JOIN with WHERE

Filtered INNER JOIN

SELECT 
    c.name,
    o.order_date,
    o.total_amount
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'
  AND o.total_amount > 100;

INNER JOIN with Sample Data

Understanding how INNER JOIN works with data:

Sample Data

Customers:
┌─────────────┬─────────────┐
│ customer_id │ name        │
├─────────────┼─────────────┤
│ 1           │ John Doe    │
│ 2           │ Jane Smith  │
│ 3           │ Bob Johnson │
└─────────────┴─────────────┘

Orders:
┌──────────┬─────────────┬──────────────┐
│ order_id │ customer_id │ total_amount │
├──────────┼─────────────┼──────────────┤
│ 1        │ 1           │ 100.00       │
│ 2        │ 1           │ 200.00       │
│ 3        │ 2           │ 150.00       │
└──────────┴─────────────┴──────────────┘

INNER JOIN Result:
┌─────────────┬──────────┬──────────────┐
│ name        │ order_id │ total_amount │
├─────────────┼──────────┼──────────────┤
│ John Doe    │ 1        │ 100.00       │
│ John Doe    │ 2        │ 200.00       │
│ Jane Smith  │ 3        │ 150.00       │
└─────────────┴──────────┴──────────────┘

Note: Bob Johnson is excluded (no orders)

INNER JOIN vs Other JOINs

Understanding the difference:

Comparison

INNER JOIN:    Only matching rows from both tables
LEFT JOIN:     All rows from left table + matching from right
RIGHT JOIN:    All rows from right table + matching from left
FULL JOIN:     All rows from both tables

INNER JOIN with Aggregates

Use INNER JOIN with GROUP BY for summaries:

Aggregate with INNER JOIN

SELECT 
    c.name AS customer_name,
    COUNT(o.order_id) AS order_count,
    SUM(o.total_amount) AS total_spent
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

Multiple Conditions in ON Clause

You can specify multiple conditions in the ON clause:

Multiple join conditions

SELECT *
FROM Orders o
INNER JOIN OrderItems oi 
ON o.order_id = oi.order_id 
AND o.status = 'Shipped';

INNER JOIN is Default

In most databases, JOIN without a keyword defaults to INNER JOIN:

Default JOIN

-- These are equivalent:
SELECT * FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id;

SELECT * FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id;

When to Use INNER JOIN

When you need records that exist in both tables
When you want to exclude unmatched rows
For filtering relationships (e.g., customers with orders)
When performance is critical (usually fastest JOIN type)
For mandatory relationships between tables

Performance Considerations

Fastest JOIN type: INNER JOIN is typically the most efficient
Index both columns: Index columns used in ON clause
Filter first: Use WHERE to reduce rows before joining
Use appropriate indexes: Foreign keys should be indexed

Best Practices

Always use ON clause: Explicitly specify join conditions
Use table aliases: Make queries more readable
Index join columns: Improve performance significantly
Verify relationships: Understand how tables relate
Consider NULLs: NULL values don't match in JOINs

Common Mistakes

Missing ON clause: Results in CROSS JOIN (Cartesian product)
Wrong join columns: Using incorrect relationship columns
Forgetting unmatched rows: INNER JOIN excludes non-matching rows
NULL values: NULLs don't match, so rows are excluded

Next Steps

Learn about including all rows from one table with LEFT JOIN or RIGHT JOIN.

← Joins Left Join →