INNER JOIN in MySQL

The INNER JOIN Rule

INNER JOIN answers an intersection question: return only combinations whose join predicate is TRUE. It does not first merge entire tables; conceptually it tests candidate pairs, while the optimizer may choose a more efficient physical order.

Syntax and Verified Output

SELECT s.student_id, s.student_name,
       c.class_name, c.teacher_name
FROM join_students AS s
INNER JOIN classes AS c
  ON c.class_id = s.class_id
ORDER BY s.student_id;

The query returns four rows. Vihaan has NULL class_id, and class XI-A has no student. Neither has a TRUE pairing. Qualifying class_id prevents ambiguity because both tables contain that column name.

ON Versus USING

When the columns have the same name, this equivalent form is concise:

SELECT s.student_name, c.class_name
FROM join_students AS s
INNER JOIN classes AS c
USING (class_id);

USING (class_id) also coalesces the same-named join column for an unqualified projection. Prefer ON in teaching and production when names differ, when more predicates are needed, or when explicit qualification prevents confusion.

Cardinality and Duplicate-Looking Rows

In the lab, class_id is unique in classes. Two students match X-A and two match X-B, so four student rows yield four result rows. If classes accidentally contained two rows for class_id 10, both Aarav and Meera would each match twice.

SELECT c.class_id, COUNT(*) AS matched_students
FROM join_students AS s
JOIN classes AS c
  ON c.class_id = s.class_id
GROUP BY c.class_id
ORDER BY c.class_id;

These repetitions are relational evidence, not automatically duplicates. Enforce unique keys where the model promises uniqueness and inspect all key parts before considering DISTINCT.

Composite-Key Matches

If a result is uniquely identified by student and subject, both columns belong in a relation that compares results from two terms:

SELECT t1.student_id, t1.subject_code,
       t1.marks AS term1_marks,
       t2.marks AS term2_marks
FROM term1_results AS t1
JOIN term2_results AS t2
  ON t2.student_id = t1.student_id
 AND t2.subject_code = t1.subject_code;

Joining only on student_id would pair each Term 1 subject with every Term 2 subject for that student. The correct ON clause represents the complete business key.

Filtering, NULL and Predicate Meaning

SELECT s.student_name, c.class_name
FROM join_students AS s
JOIN classes AS c
  ON c.class_id = s.class_id
WHERE c.teacher_name = 'Ms. Rao';

For an inner join, a simple condition on one table can often be moved between ON and WHERE without changing the final rows, and the optimizer may reorder work. Still, place relationship predicates in ON and final-result filters in WHERE for readable intent. Equality never matches NULL; do not convert unknown identifiers to zero merely to force a match.

Performance and Practice

• Keep the referenced key indexed and normally index the foreign-key side used heavily for joins.
• Use compatible numeric or string types; mismatched collations and conversions can add work.
• Do not wrap indexed join columns in functions unless a designed functional index supports it.
• Start with correct results, then inspect EXPLAIN; optimizer join order need not match written order.

EXPLAIN
SELECT s.student_name, c.class_name
FROM join_students AS s
JOIN classes AS c
  ON c.class_id = s.class_id;

Practice: list the four assigned students with room_no; count matches per teacher; then predict the effect of removing the ON clause before testing in a disposable database.

Official References

• MySQL 8.4 Reference Manual: JOIN Clause
• MySQL 8.4: Nested Join Optimization
• MySQL 8.4: EXPLAIN Statement

Syntax and optimizer notes were checked against the official MySQL 8.4 manual. Always test plans and row counts on your own schema and data.