INNER JOIN

Inner Join in MySQL

Introduction
Related data across multiple tables requires joining on common columns.
INNER JOIN returns only matching rows from both tables.
It is widely used for:

• Combining master-detail relationships
• Normalized database queries
• Report generation

What is an Inner Join
INNER JOIN returns rows when there is a match in both tables.
Non-matching rows from either table are excluded.

Basic Syntax

sql

SELECT columns 

FROM table1 

INNER JOIN table2 ON table1.column = table2.column; 

Example

sql

SELECT s.name, c.course_name 

FROM Students s 

INNER JOIN Courses c ON s.course_id = c.id; 

Only students enrolled in courses (matching course_id).

INNER JOIN with WHERE
Additional filtering after join.

sql

SELECT s.name, c.course_name 

FROM Students s 

INNER JOIN Courses c ON s.course_id = c.id 

WHERE c.duration > 6; 

Multiple INNER JOINs
Join more than 2 tables.

sql

SELECT s.name, c.course_name, d.department_name 

FROM Students s 

INNER JOIN Courses c ON s.course_id = c.id 

INNER JOIN Departments d ON c.dept_id = d.id; 

INNER JOIN with ORDER BY
Join them and sort the results.

sql

SELECT s.name, s.marks, c.course_name 

FROM Students s 

INNER JOIN Courses c ON s.course_id = c.id 

ORDER BY s.marks DESC; 

INNER JOIN with GROUP BY
Aggregate joined data.

sql

SELECT c.course_name, COUNT(s.id) as enrollment 

FROM Students s 

INNER JOIN Courses c ON s.course_id = c.id 

GROUP BY c.course_name; 

INNER JOIN Multiple Conditions
Complex join conditions.

sql

SELECT s.name, c.course_name 

FROM Students s 

INNER JOIN Courses c ON s.course_id = c.id 

                 AND c.status = 'Active'; 

INNER JOIN with USING
Same column name shorthand.

sql

SELECT s.name, c.course_name 

FROM Students s 

INNER JOIN Courses c USING(course_id); 

Comma Syntax (Old Style)
Pre-ANSI equivalent.

sql

SELECT s.name, c.course_name 

FROM Students s, Courses c 

WHERE s.course_id = c.id; 

INNER JOIN with Subquery
Join against subquery results.

sql

SELECT s.name, sc.avg_marks 

FROM Students s 

INNER JOIN ( 

    SELECT course_id, AVG(marks) as avg_marks 

    FROM StudentMarks GROUP BY course_id 

) sc ON s.course_id = sc.course_id; 

Execution Order with INNER JOIN
FROM → INNER JOIN → ON → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Performance Considerations

• Index both join columns
• INNER JOIN order doesn't matter (optimizer decides)
• Use STRAIGHT_JOIN to force order
• Avoid functions on join columns

Important Notes

• Only matching rows returned
• Cartesian product if no ON condition
• USING requires the same column names
• Multiple matches create duplicate rows
• NULLs never match in joins

Example Scenario
Complete student-course-department report:

sql

SELECT

    s.name,

    s.marks,

    c.course_name,

    d.department_name

FROM Students s

INNER JOIN Courses c ON s.course_id = c.id

INNER JOIN Departments d ON c.dept_id = d.id

WHERE s.marks > 80

ORDER BY s.marks DESC, c.course_name

LIMIT 50;

Common Mistakes

• Forgetting the ON condition (cartesian explosion)
• Non-indexed join columns (slow)
• Functions on join columns (no index)
• Cartesian product from wrong join order
• Expecting non-matching rows

Key Points to Remember

• INNER JOIN returns only matching rows
• ON clause defines the match condition
• Index both sides of the join
• Multiple tables chain with additional INNER JOIN
• USING shorthand for the same column names
• No matches = empty result