How do you write a basic INNER JOIN query between two tables?
Writing a Basic INNER JOIN Query in MySQL
An INNER JOIN returns only the rows where there is a matching value in both tables. It is the most commonly used type of JOIN in SQL.
- 1
- 2
SELECT columns
- 3
FROM table1
- 4
INNER JOIN table2 ON table1.common_column = table2.common_column;
- 5
- 1
Suppose you have two tables:
- 2
•
users(id, name) - 3
•
orders(id, user_id, amount) - 4
- 5
Query:
- 6
- 7
SELECT u.id, u.name, o.amount
- 8
FROM users u
- 9
INNER JOIN orders o ON u.id = o.user_id;
- 10
- 11
- 12
• This returns only the users who have placed at least one order.
Use INNER JOIN when you want to fetch only the matching records from both tables.
Scenario Questions
- 1
You have two tables: users and orders. Users has id and name, orders has user_id and amount. How would you write a query to get the name of every user who placed an order?
- 2
You run a query joining users and orders but get way more rows than expected. What’s the most likely mistake, and how would you fix it?
- 3
You need to show user names and their total order amounts. How would you structure the INNER JOIN and what columns would you SELECT?
- 1
A feature that shows customer order history suddenly started returning empty results. The tables haven’t changed — what would you check in the JOIN logic?
- 2
Your team’s query joins users and orders on user_id, but sometimes users are soft-deleted. Should you still use INNER JOIN? Why or why not?
- 3
You’re debugging a slow report that joins users and orders. The join column is indexed, but it’s still slow. What else could be wrong?
- 1
You’re optimizing a dashboard that joins 5 tables including users, orders, products, and regions. The query runs in 8 seconds — how would you approach reducing latency without changing the business logic?
- 2
The orders table has 10M rows and users has 1M. The JOIN is on user_id, but the index is on (user_id, created_at). Is that optimal? What would you change?
- 3
Two teams use the same users-orders JOIN in different services. One uses it for analytics, the other for real-time UI. How would you ensure performance and correctness across both?
- 1
You’re migrating from a monolith to microservices, and the users-orders JOIN is now split across two services. How do you handle this join without breaking existing reports?
- 2
The legacy orders table has inconsistent user_id values — some are strings, some are NULL, some are invalid. How would you design a long-term solution for reporting that depends on this JOIN?
- 3
Your company’s analytics platform runs hundreds of JOIN-heavy queries daily. How would you standardize JOIN patterns across teams to prevent performance regressions and data inconsistencies over time?
Follow-up Questions
- ❓What happens if one of the tables has NULL in the join column?
- ❓How would you verify the result set size matches your expectation?
- ❓Why not use a LEFT JOIN here instead?