Joins in MySQL

What a JOIN Does

A relational design keeps each fact in its proper table. A student stores a class identifier; the class name, room and teacher belong in the classes table. A JOIN reconstructs the useful view at query time by testing a relationship for pairs of rows.

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

JOIN without a qualifier means INNER JOIN. Aliases keep long queries readable, but each selected column should still be qualified when both tables may contain the same name.

Verified School Lab

Run this isolated setup once. The special name join_students avoids collision with tables created in earlier lessons.

CREATE TABLE classes (
  class_id INT PRIMARY KEY,
  class_name VARCHAR(20) NOT NULL,
  room_no VARCHAR(20) NOT NULL,
  teacher_name VARCHAR(60) NOT NULL
);

CREATE TABLE join_students (
  student_id INT PRIMARY KEY,
  student_name VARCHAR(60) NOT NULL,
  class_id INT NULL,
  CONSTRAINT fk_join_student_class
    FOREIGN KEY (class_id) REFERENCES classes(class_id)
);

INSERT INTO classes VALUES
(10, 'X-A', 'Room 101', 'Ms. Rao'),
(20, 'X-B', 'Room 102', 'Mr. Sen'),
(30, 'XI-A', 'Room 201', 'Ms. Iyer');

INSERT INTO join_students VALUES
(1, 'Aarav', 10), (2, 'Meera', 10),
(3, 'Kabir', 20), (4, 'Sana', 20),
(5, 'Vihaan', NULL);

This deliberate data shape gives four matched students, one student without a class and one class without students. Those edge cases make every later output testable.

JOIN Types: Choose by Preservation

The table written first is not automatically the important table. Importance is expressed by the join type and side you preserve.

First INNER JOIN with Verified Output

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

Vihaan is excluded because NULL does not equal a class identifier. XI-A is excluded because no student matches class 30. Four matches therefore produce four rows.

Keys, Cardinality and Row Counts

classes.class_id is unique, while several students may share one class_id. This is a many-to-one join from students to classes, so each matched student produces at most one result row. If the joined column is not unique on either side, the result may expand rapidly.

• One-to-one: at most one match per row.
• One-to-many: a parent can create several output rows.
• Many-to-many: use a junction table; output follows the junction matches.

NULL and Filter Placement

Equality with NULL is UNKNOWN, not TRUE. Outer joins represent a missing partner by returning NULL for that side. To find missing partners, test a non-nullable key with IS NULL.

SELECT s.student_name
FROM join_students AS s
LEFT JOIN classes AS c
  ON c.class_id = s.class_id
WHERE c.class_id IS NULL;

For an outer join, a condition in ON controls which right rows may match while preserving left rows. The same condition in WHERE filters the completed result and can remove NULL-extended rows, effectively changing the answer.

Performance Checklist

1. Index primary and foreign-key join columns; the parent primary key is already indexed.
2. Join compatible data types and collations. Avoid functions or implicit conversions on indexed join columns.
3. Select required columns instead of SELECT *.
4. Use EXPLAIN to inspect access type, chosen key and estimated rows.
5. Validate actual row counts with representative data; an index cannot fix incorrect logic.

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 and Summary

1. Return all classes and the number of students in each, including zero.
2. Find students whose class_id is missing.
3. Predict the row count before running a CROSS JOIN between three classes and two shifts.

Summary: JOIN is matching plus preservation. State the required population, write the relationship in ON, predict cardinality, run the query and verify unmatched cases. This habit is more valuable than memorizing diagrams.

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.