How to Use INNER JOIN in SQL

Use INNER JOIN when you need only rows that exist in both related tables. This is the cleanest way to combine matching records like users with orders, products with categories, or students with enrollments.

What you’ll build or solve

You’ll learn how to use INNER JOIN in SQL to return only matching rows. You’ll also know how to avoid incorrect join conditions and duplicate results.

When this approach works best

This approach is the right choice when unmatched rows should be excluded.

Common real-world scenarios include:

• Customers with purchases
• Products with categories
• Students with enrollments
• Orders with payments
• Employees with departments

This is a bad idea when rows from the left table should still appear even without a match. In that case, use LEFT JOIN.

Prerequisites

You only need:

• Two related SQL tables
• Basic SELECT knowledge
• A shared key such as user_id

Step-by-step instructions

Step 1: Join matching rows with INNER JOIN

Start with the first table in FROM, then join the second with ON.

SELECT users.first_name, orders.total
FROM users
INNER JOIN orders
ON users.user_id = orders.user_id;

This returns only users who actually have orders.

The shared key in ON controls how the tables connect.

Use aliases to keep larger joins readable.

SELECT u.first_name, o.total
FROM users u
INNER JOIN orders o
ON u.user_id = o.user_id;

What to look for:

• Only matching rows appear
• Unmatched left or right rows are excluded
• The ON key must reflect the relationship
• Great for normalized data
• Aliases improve readability

Examples you can copy

Customers with purchases

SELECT c.name, o.order_id
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id;

Products with categories

SELECT p.name, c.category_name
FROM products p
INNER JOIN categories c
ON p.category_id = c.category_id;

Students with classes

SELECT s.name, e.course_id
FROM students s
INNER JOIN enrollments e
ON s.student_id = e.student_id;

Common mistakes and how to fix them

Mistake 1: Joining unrelated columns

What the reader might do:

ON users.email = orders.user_id

Why it breaks: unrelated fields create invalid matches.

Corrected approach:

ON users.user_id = orders.user_id

Mistake 2: Expecting unmatched rows to appear

What the reader might do:

SELECT users.email, profiles.bio
FROM users
INNER JOIN profiles
ON users.user_id = profiles.user_id;

Why it breaks: users without profiles disappear.

Corrected approach:

Use LEFT JOIN when unmatched left rows should stay visible.

Mistake 3: Duplicate rows from one-to-many relationships

What the reader might do:

SELECT users.email, orders.total
FROM users
INNER JOIN orders
ON users.user_id = orders.user_id;

Why it breaks: one user with multiple orders appears multiple times.

Corrected approach:

Use aggregation or DISTINCT when one row per user is required.

Troubleshooting

If rows disappear, confirm INNER JOIN is actually the intended behavior.

If duplicates appear, check for one-to-many relationships.

If too many rows appear, verify the ON condition.

If the query is hard to read, use aliases.

Quick recap

• Use INNER JOIN for matching rows only
• Unmatched rows are excluded
• Join on meaningful shared keys
• Aliases improve readability
• Use LEFT JOIN when left rows must remain