INNER JOIN

Introduction

INNER JOIN returns rows where the join condition matches in both tables. Unmatched rows are dropped. The default and most-used join.

Understanding the topic

Core concepts to understand:

• FROM a JOIN b ON a.id = b.a_id.
• Drops rows with no match on either side.
• Multiple inner joins chain naturally.
• Best with indexed join columns.
• Equivalent to WHERE with the same predicate (older syntax).

Syntax reference

Visual workflow / architecture:

users          orders
┌────┬──┐      ┌──────┬─────┐
│ 1  │A │      │ user │amt  │
│ 2  │B │      │  1   │ 10  │
│ 3  │C │      │  1   │ 20  │
└────┴──┘      │  2   │ 30  │ (no match for 3)
               └──────┴─────┘

INNER JOIN ON users.id = orders.user_id
 → 1A:10, 1A:20, 2B:30   (3C dropped)

SELECT <cols>
FROM   <a> [AS a]
INNER JOIN <b> [AS b]
       ON  <a.key> = <b.key>;

SELECT u.name, o.amount
FROM   users  u
INNER JOIN orders o ON o.user_id = u.id;

Real-world use

Every joined report — orders + users, posts + authors, invoices + customers — uses INNER JOIN by default.

Best practices

• Index both sides of the join.
• Always alias tables in joins.
• Be explicit: INNER JOIN over comma-style joins.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. What does INNER JOIN return?
• Q2. Difference vs CROSS JOIN.
• Q3. Performance impact of unindexed join columns.
• Q4. Equivalent old-style syntax.
• Q5. Inner join with multiple tables — how it chains.