Mastering INNER JOIN in SQL: Complete Guide with Examples & Practice Questions (2026 Interviews)

When I first started preparing for SQL interviews, INNER JOIN was the concept that tripped me up the most. I could handle single-table queries fine, but as soon as they asked something like "show user names with their orders," I froze.

So I decided to master it properly — from zero understanding to solving complex multi-table problems. This article is exactly that journey: everything I learned, step by step, with simple explanations and practice questions you can solve right away.

By the end, you'll feel confident tackling any INNER JOIN question in interviews (TCS, Infosys, Deloitte — they love these!).

We'll use the same realistic database throughout (users, products, orders) — you can practice every question live here: sqlpractice.in

What is a JOIN? (Quick Recap)

Before INNER JOIN, let's remember why we need JOINs at all.

Imagine you have two separate tables:

users: id, name, email
orders: id, user_id, amount, product

A single table can only tell part of the story. To answer "Which users placed orders and how much?" — you need to combine data from both tables.

That's what JOIN does.

There are different types (INNER, LEFT, RIGHT, FULL), but today we're focusing on INNER JOIN — the most common and important one.

Visualizing INNER JOIN (The Venn Diagram)

The easiest way to understand INNER JOIN is with a Venn diagram.

The overlapping area (center) = rows that exist in both tables.
INNER JOIN returns only these matching rows.
If a user has no orders → not included.
If an order has no matching user → not included.

Simple rule: Only matches survive.

Basic Syntax of INNER JOIN

Here's the standard way to write it:

Mastering INNER JOIN in SQL - sqlpractice

You can also use aliases (short names) for cleaner code:

There's also the USING clause if column names are identical:

Practice Question 1 (Easy) Show first_name, last_name, and province_name for all patients.

Practice this question live →

Joining More Than Two Tables

Real life rarely has just two tables. What if we want user name + product name + order quantity?

We chain JOINs!

master inner join in sql - sqlpractice.in

Practice Question 2 (Medium) Show admission_id, patient_id, and doctor's last_name for admissions attended by a 'Cardiologist'.

Practice here →

Common Mistakes I Made (And How to Avoid Them)

When I was learning, these got me every time:

Forgetting the ON clause → results in Cartesian product (every row × every row = explosion!)
Wrong column names → no matches → empty result
Expecting all rows → remember, INNER JOIN drops non-matches
Bad alias order → hard to read/debug

Practice Question 3 (Medium) Show admission_id, admission_date, and patient's city for patients from 'ON' (Ontario).

Practice here →

INNER JOIN vs LEFT JOIN: When to Choose Which?

Quick comparison:

INNER JOIN: Only matching rows → "I want only complete records"
LEFT JOIN: All rows from left table + matches from right → "I want all users, even if no orders"

Use INNER when you need complete data only.

Use LEFT when you want everything from one side.

Practice Question 4 (Medium) Show patient first_name and their total number of admissions.

Practice here →

Advanced Patterns with INNER JOIN

Now let's level up:

With Aggregation
Multiple Conditions in ON

Practice Question 5–7 (Hard)

Show average admission_cost for each doctor specialty, sorted by cost descending.
Show province_name and the highest admission_cost recorded in that province.
Show patient first_name, doctor last_name, and diagnosis for admissions costing more than 5000.

Practice these →

Performance Tips

INNER JOIN can be slow on large tables if not optimized:

Always index foreign key columns (user_id, product_id)
Avoid SELECT * — select only needed columns
Use EXPLAIN ANALYZE to check query plan

Practice Question 8 (Expert) Calculate the total admission_cost for all patients in each province. Show province_name and total cost.

Practice here →

Final Thoughts

I started this article not really understanding INNER JOIN, and now — after writing it and solving these questions — I feel confident.

You can too.

Go through the practice questions in order (easy to hard). Solve them on sqlpractice.in — instant feedback, no signup needed.

Once you're comfortable with INNER JOIN, LEFT JOIN will feel natural (coming in the next article!).

Keep practicing — one concept at a time.

You've got this! 💪

If this helped, share it with someone preparing for interviews. Next up: Mastering LEFT JOIN.