INNER JOIN in SQL with Matching Row Examples

INNER JOIN is the most commonly used JOIN type in SQL, and in most database systems, writing JOIN without any qualifier defaults to an INNER JOIN. It is the JOIN you reach for whenever you only want rows that have a valid, matching relationship in both tables — for example, only employees who are actually assigned to a real department, or only orders that belong to a real, existing customer.

Understanding INNER JOIN thoroughly is essential because it is the baseline against which every other JOIN type (LEFT, RIGHT, FULL) is compared. Interviewers frequently ask candidates to explain, with an example, exactly which rows an INNER JOIN would exclude compared to a LEFT JOIN, so precision in this lesson pays off across the rest of the module.

Key Definitions

• INNER JOIN: A JOIN that returns only the rows where the join condition is satisfied in both tables being combined.
• Matching row: A row in one table whose join column value exists in the corresponding join column of the other table.
• Unmatched row: A row that has no corresponding match in the other table and is therefore excluded from an INNER JOIN result.
• Equi-join: A JOIN whose condition uses an equality comparison, the most common form of INNER JOIN.

What You'll Learn

• Define INNER JOIN and explain that it returns only rows with matches in both tables.
• Write INNER JOIN queries using standard MySQL syntax.
• Predict which rows will be excluded from an INNER JOIN result.
• Differentiate INNER JOIN from the plain JOIN keyword.
• Apply INNER JOIN across more than two tables in a single query.

Detailed Explanation

INNER JOIN works by examining every row in the first table and checking whether its join column value exists in the second table. Only when a match is found does the combined row appear in the output. If an employee's department_id does not correspond to any row in the departments table — perhaps because the department was deleted or the value is NULL — that employee is silently excluded from the INNER JOIN result entirely.

This exclusion behavior is the defining characteristic of INNER JOIN and the reason it must be chosen carefully. If your goal is a report that should show every employee regardless of whether their department exists, INNER JOIN is the wrong choice — you would want LEFT JOIN instead, covered in the next lesson. But when your goal genuinely requires only valid, matched relationships — such as 'show me only orders that belong to an active customer account' — INNER JOIN is precisely correct and typically the fastest option since it has less work to do than outer joins in most query planners.

In MySQL and standard SQL, writing JOIN alone (without INNER, LEFT, or RIGHT) is treated identically to INNER JOIN. Many professional style guides still recommend writing INNER JOIN explicitly for clarity, so that anyone reading the query immediately understands the intended behavior without needing to know the default.

Visual Summary

Draw two overlapping circles (a Venn diagram) labeled 'employees' and 'departments'. Shade only the overlapping middle region and label it 'INNER JOIN result — rows present in both tables'. Leave the non-overlapping left and right regions unshaded and label them 'excluded rows — no match found'.

Quick Reference

employee_name	department_id (employees)	Matches a department?	Appears in INNER JOIN result?
Asha Mehta	1	Yes	Yes
Rahul Sharma	3	Yes	Yes
Priya Nair	NULL	No	No
Arjun Das	9 (deleted dept)	No	No

SQL Example

-- Only employees whose department_id has a real matching row
SELECT
  e.employee_name,
  d.department_name
FROM employees e
INNER JOIN departments d
  ON e.department_id = d.department_id;

-- INNER JOIN across three tables
SELECT
  e.employee_name,
  d.department_name,
  p.project_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN project_assignments pa ON e.employee_id = pa.employee_id
INNER JOIN projects p ON pa.project_id = p.project_id;

The first query returns only employees whose department_id exists as a real row in departments — employees with a NULL department_id or a reference to a deleted department are silently dropped. The three-table query extends the same principle: an employee only appears in the final result if they have a matching department AND a matching project assignment AND that project still exists, since INNER JOIN requires a match at every step of the chain.

Real-World Examples

• An e-commerce report showing 'orders with valid customer accounts' uses INNER JOIN to automatically exclude any orphaned orders left behind by deleted test accounts.
• A payroll system joining employees to salary_grades with INNER JOIN ensures only employees with an assigned, valid grade appear on the payslip run.
• A learning platform joining students to completed_courses with INNER JOIN produces a certificate-eligibility list containing only students who actually finished a course.
• An inventory system joining products to suppliers with INNER JOIN reports only products that currently have an active, matched supplier.
• A banking system joining transactions to accounts with INNER JOIN ensures fraud-detection reports never include transactions pointing to closed or non-existent accounts.

Common Mistakes to Avoid

• Assuming INNER JOIN will show all rows from the left table even without a match, confusing it with LEFT JOIN.
• Forgetting that a NULL foreign key value excludes a row from an INNER JOIN result.
• Not realizing that chaining several INNER JOINs compounds exclusion, potentially dropping more rows than expected.
• Writing INNER JOIN when the actual business requirement calls for preserving unmatched rows (which needs LEFT/RIGHT JOIN instead).
• Overlooking that deleted or orphaned foreign key references silently vanish from INNER JOIN results without any warning or error.

Interview Questions

Q1. What does INNER JOIN return that other joins might not?

INNER JOIN returns only rows that have a matching value in both joined tables. Any row from either table without a corresponding match is excluded entirely from the result, unlike LEFT, RIGHT, or FULL OUTER JOIN, which preserve unmatched rows from one or both sides.

Q2. Is there a difference between JOIN and INNER JOIN in MySQL?

No functional difference. In MySQL and standard SQL, the JOIN keyword defaults to INNER JOIN behavior. Writing INNER JOIN explicitly is a stylistic choice that improves query readability.

Q3. What happens to a row with a NULL foreign key value in an INNER JOIN?

A row with a NULL foreign key cannot match any value in the referenced table's primary key column, since NULL is never considered equal to anything, including another NULL. That row is therefore excluded from the INNER JOIN result.

Q4. How does INNER JOIN behave when chaining three or more tables?

Each additional INNER JOIN clause further restricts the result: a row survives in the final output only if it has a match at every join step in the chain. Missing a match at any single step removes the row entirely.

Q5. When should you avoid using INNER JOIN?

Avoid INNER JOIN when you need to preserve unmatched rows from one table, such as showing all customers even those with zero orders. In that case, a LEFT JOIN should be used instead.

Practice MCQs

1. INNER JOIN returns:

1. All rows from the left table only
2. All rows from both tables regardless of match
3. Only rows with matching values in both tables
4. Only unmatched rows

Answer: C. Only rows with matching values in both tables

Explanation: INNER JOIN's defining behavior is excluding any row that lacks a match in the other table.

2. In MySQL, writing 'JOIN' without a qualifier behaves like:

1. CROSS JOIN
2. LEFT JOIN
3. INNER JOIN
4. SELF JOIN

Answer: C. INNER JOIN

Explanation: By default, an unqualified JOIN keyword is treated as an INNER JOIN in MySQL and standard SQL.

3. A row with a NULL foreign key in an INNER JOIN will:

1. Always appear with NULL values from the other table
2. Be excluded from the result
3. Cause a syntax error
4. Match every row in the other table

Answer: B. Be excluded from the result

Explanation: NULL never equals any value, so a row with a NULL join column cannot match and is dropped from an INNER JOIN result.

4. When chaining INNER JOIN across three tables, a row appears in the final result only if:

1. It matches in at least one join
2. It matches in every join in the chain
3. The first table has more rows
4. An index exists on all tables

Answer: B. It matches in every join in the chain

Explanation: INNER JOIN requires a match at each step; failing to match at any single join removes the row from the final combined result.

5. INNER JOIN is best suited for reports that should:

1. Show every row regardless of matches
2. Show only rows with confirmed relationships in all joined tables
3. Duplicate every row
4. Ignore join conditions

Answer: B. Show only rows with confirmed relationships in all joined tables

Explanation: INNER JOIN is ideal when only valid, matched relationships across tables are meaningful for the report.

Quick Revision Points

• INNER JOIN returns only rows with matching values in both joined tables — the most commonly tested JOIN definition.
• JOIN and INNER JOIN are functionally identical in MySQL; INNER is the implicit default.
• NULL foreign key values never match and are excluded from INNER JOIN results.
• Chaining INNER JOINs across N tables requires a match at every step for a row to survive.
• INNER JOIN is generally the most performant JOIN type since it does the least extra work compared to outer joins.

Conclusion

• INNER JOIN is the default, most common JOIN and returns only genuinely matched rows across tables.
• Explicitly writing INNER JOIN (instead of relying on the JOIN default) improves query clarity for teams.
• NULL and orphaned foreign keys are silently dropped by INNER JOIN, which can hide data quality issues if not checked.
• Chained INNER JOINs across multiple tables require matches at every link, compounding exclusions.

INNER JOIN combines rows from two or more tables but includes only those rows where the join condition finds a match on both sides. Rows with NULL or unmatched foreign key values are silently excluded. In MySQL, INNER JOIN is the default behavior for the plain JOIN keyword, and it remains the most frequently used and typically fastest join type, ideal whenever a report should only reflect confirmed, valid relationships between tables.