INNER JOIN returns only the rows that have a match in both tables. It's the default, most commonly used join type.
🔵 Shaded = only the overlap is returned — rows with a match on both sides.
Reference Tables Used in This Section
Every join note below uses these same two tables, so you can see exactly how each join type treats the same data differently.
employees
| id | name | department_id |
|---|---|---|
| 1 | Aman | 1 |
| 2 | Riya | 2 |
| 3 | Karan | 1 |
| 4 | Neha | NULL |
departments
| id | department_name |
|---|---|
| 1 | Engineering |
| 2 | Marketing |
| 3 | Sales |
Notice: Neha has no department (department_id is NULL), and Sales has no employees at all. These two "unmatched" rows are what make the differences between join types visible.
Syntax
SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;INNER is optional in most databases — JOIN alone defaults to an inner join.
Example
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;Expected Output:
| name | department_name |
|---|---|
| Aman | Engineering |
| Riya | Marketing |
| Karan | Engineering |
3 rows, not 4 and not 3+3. Neha is excluded (her department_id is NULL — nothing to match). Sales is excluded (no employee has department_id = 3).
Practical Use Case
Any report where a row without a match is meaningless — e.g. "list employees and their department names." An employee with no department wouldn't have anything sensible to show in that column anyway, so excluding them is often correct.
Common Mistakes
- Using
INNER JOINwhen you actually need unmatched rows too (e.g. "show me ALL employees, including those without a department") — that calls for LEFT JOIN instead - Joining on a column with mismatched data types (e.g. an
INTID vs aVARCHARID) — this can silently return zero rows or force an expensive implicit conversion - An unintentional many-to-many join (join column not unique on either side) silently multiplying row counts — always sanity-check row counts after a join
Interview Relevance
Q: "If employees has 4 rows and departments has 3, and you INNER JOIN them, how many rows can you get?" Trick question — it depends entirely on how many rows actually match on the join condition, not on the table sizes. In this example: exactly 3.
Practice Question
Using the reference tables, write the INNER JOIN query and confirm your result matches the 3-row output above.