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.
1️⃣ 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)
2️⃣ Basic Syntax
👉 You can also write:
3️⃣ 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 |
✔ Neha is excluded (no matching department)
4️⃣ INNER JOIN with WHERE Clause
✔ Filters after matching
5️⃣ INNER JOIN with Multiple Tables
📌 Returns rows where all three tables match
6️⃣ INNER JOIN vs LEFT JOIN ⭐ (Interview Favorite)
| Feature | INNER JOIN | LEFT JOIN |
|---|---|---|
| Matching rows only | ✔ Yes | ❌ No |
| Unmatched rows | ❌ Excluded | ✔ Included (left) |
| NULL values | ❌ No | ✔ Yes |
| Use case | Exact matches | Missing data analysis |
7️⃣ INNER JOIN vs RIGHT JOIN
| Feature | INNER JOIN | RIGHT JOIN |
|---|---|---|
| Matching rows | ✔ Yes | ✔ Yes |
| Unmatched rows | ❌ Excluded | ✔ Right table |
| NULLs | ❌ No | ✔ Yes |
8️⃣ INNER JOIN with Aggregation
✔ Shows employee count only for departments with employees
9️⃣ Common Mistakes ❌
❌ Forgetting ON condition
❌ Confusing INNER JOIN with LEFT/RIGHT JOIN
❌ Using WHERE instead of ON for 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 written 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 returned)
🔥 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 excluded
-
Most commonly used JOIN
-
INNER keyword is optional
-
Essential for SQL exams & interviews
