MySQL INNER JOIN

Last updated: October 19, 2026Oct 19, 2026

INNER JOIN returns only the rows that have a match in both tables. It is the default join (plain JOIN means INNER JOIN) and the one you'll use most: orders with their customers, students with their courses, products with their categories.

1. What INNER JOIN Returns

SELECT columns
FROM table1
INNER JOIN table2 ON table1.col = table2.col;

2. A Basic INNER JOIN

SELECT s.name, d.dept_name
FROM students s
INNER JOIN departments d ON d.dept_id = s.dept_id
ORDER BY s.name;

Result: 4 rows

Diya (no department) and History (no students) are missing, because they have no match. Use a LEFT JOIN to keep them.

3. USING and Aliases

SELECT name, dept_name FROM students JOIN departments USING (dept_id) WHERE dept_name = 'Physics';

Result: 2 rows

Column names that exist in both tables must be qualified, or MySQL reports an ambiguity:

SELECT name, dept_id, dept_name FROM students JOIN departments ON departments.dept_id = students.dept_id;

ERROR 1052 (23000): Column 'dept_id' in field list is ambiguous

4. One-to-Many: Rows Repeat

A student with two enrolments appears twice. That's correct: each output row is one pair of matching rows:

SELECT s.name, e.course_id, e.grade
FROM students s
JOIN enrolments e ON e.student_id = s.student_id
ORDER BY s.name;

Result: 5 rows

5. Joining Three Tables

SELECT s.name, c.title, d.dept_name AS course_dept, e.grade
FROM enrolments e
JOIN students    s ON s.student_id = e.student_id
JOIN courses     c ON c.course_id  = e.course_id
JOIN departments d ON d.dept_id    = c.dept_id
ORDER BY s.name, c.title;

Result: 5 rows

6. Filtering and Aggregating Joined Data

SELECT c.title, COUNT(*) AS students, GROUP_CONCAT(s.name ORDER BY s.name) AS who
FROM courses c
JOIN enrolments e ON e.course_id = c.course_id
JOIN students   s ON s.student_id = e.student_id
GROUP BY c.course_id, c.title
ORDER BY students DESC;

Result: 3 rows

SELECT s.name, COUNT(*) AS courses
FROM students s JOIN enrolments e ON e.student_id = s.student_id
WHERE e.grade IN ('A', 'B')
GROUP BY s.student_id, s.name
HAVING COUNT(*) >= 2;

Result: 1 row

7. Join Conditions Other Than =

The ON condition can be any expression. Map scores to grade bands stored in another table:

CREATE TABLE bands (band CHAR(1), min_score INT, max_score INT);
INSERT INTO bands VALUES ('A',85,100),('B',70,84),('C',50,69);
CREATE TABLE exam (student VARCHAR(10), score INT);
INSERT INTO exam VALUES ('Aditi',91),('Bilal',73),('Chen',58);

SELECT x.student, x.score, b.band
FROM exam x
JOIN bands b ON x.score BETWEEN b.min_score AND b.max_score;

Result: 3 rows

8. Old Comma Syntax

Listing tables with commas and putting the condition in WHERE is an older way to write an inner join. It gives the same result, but explicit JOIN … ON is clearer and avoids accidental cross joins when a condition is forgotten:

SELECT s.name, d.dept_name FROM students s, departments d WHERE d.dept_id = s.dept_id ORDER BY s.name;

Result: 4 rows

9. How MySQL Executes It

EXPLAIN SELECT s.name, d.dept_name FROM students s JOIN departments d ON d.dept_id = s.dept_id;

Result: 2 rows

With only a handful of rows, reading both tables and matching them in memory with a hash join is cheapest, and that is what the plan shows. On large tables, an index on the join column lets MySQL look up each match directly instead (access type eq_ref through a primary or unique key, or ref through a normal index). Hash joins are MySQL 8's fallback when no useful index exists.

10. Practice Exercises

SELECT c.title, d.dept_name FROM courses c JOIN departments d ON d.dept_id = c.dept_id ORDER BY d.dept_name, c.title;

SELECT s.name, c.title
FROM enrolments e
JOIN students s ON s.student_id = e.student_id
JOIN courses  c ON c.course_id  = e.course_id
WHERE c.dept_id <> s.dept_id;

11. Summary

• INNER JOIN (or JOIN) returns only pairs of rows that match in both tables.
• Write the match in ON (or USING for same-named columns); qualify shared column names.
• One-to-many joins repeat rows on the "one" side; aggregate carefully.
• Chain several JOINs for several tables; conditions can be ranges, not just =.