MySQL INNER JOIN
MySQL INNER JOIN
The INNER JOIN in MySQL is used to retrieve only the rows that have matching values in both tables.
- If there is no match, the row is excluded from the result.
What is INNER JOIN?
Returns only matching records from both tables
Rows without a match in either table are not shown
Most commonly used JOIN in SQL
Also called JOIN (INNER is optional keyword)
Basic Syntax
You can also write:
Simple Example
Table: employees
| emp_id | emp_name | dept_id |
|---|---|---|
| 1 | Amit | 10 |
| 2 | Ravi | 20 |
| 3 | Neha | 30 |
Table: departments
| dept_id | dept_name |
|---|---|
| 10 | HR |
| 20 | IT |
INNER JOIN Query
Result
| emp_name | dept_name |
|---|---|
| Amit | HR |
| Ravi | IT |
Nehais excluded (no matching department)
INNER JOIN with WHERE Clause
INNER JOIN combines rows from tables only when the join condition matches in both tables. The WHERE clause is used to apply additional filtering after the join is formed. Only rows satisfying both the join condition and the WHERE condition appear in the result.
Example
INNER JOIN with Multiple Tables
INNER JOIN can be use to join more than two tables in a single query. Each join uses its own condition to relate tables step by step. Only rows matching in all tables are returned.
Example
INNER JOIN vs LEFT JOIN (Interview Favorite)
INNER JOIN returns only matching rows from both tables. LEFT JOIN returns all rows from the left table and matching rows from the right table. If no match exists, LEFT JOIN fills the right table columns with NULL.
Example
INNER JOIN with Aggregation (MySQL)
INNER JOIN can be combined with aggregate functions like COUNT, SUM, and AVG. GROUP BY is used to aggregate data after the join. Only matching rows are included in the aggregation.
Example
Common Mistakes
- Forgetting
ONcondition - Confusing INNER JOIN with LEFT/RIGHT JOIN
- Using
WHEREinstead ofONfor join condition - Expecting unmatched rows to appear
Interview Questions & MCQs (Very Important)
Q1. What does INNER JOIN return?
A) All rows
B) Matching rows only
C) Left table rows
D) Right table rows
Answer: B
Q2. Which keyword is optional in INNER JOIN?
A) JOIN
B) INNER
C) ON
D) SELECT
Answer: B
Q3. If there is no matching row, what happens?
A) NULL shown
B) Error
C) Row excluded
D) Zero shown
Answer: C
Q4. INNER JOIN is also writtening as?
A) LEFT JOIN
B) RIGHT JOIN
C) JOIN
D) CROSS JOIN
Answer: C
Q5. Which join is fastest generally?
A) INNER JOIN
B) LEFT JOIN
C) RIGHT JOIN
D) CROSS JOIN
Answer: A
(Because fewer rows are returning)
Real-Life Use Cases
- Employees with departments
- Orders with customers
- Payments with users
- Products with categories
Summary
INNER JOIN returns only matching rows
Unmatched data is excluding
Most commonly used JOIN
INNER keyword is optional
Essential for SQL exams & interviews
