Join Three Tables

Think in a Relationship Path

A three-table query is not a magical new syntax. It is a sequence of relationships. Students connect to results through student_id; results connect to subjects through subject_code. Students do not need a direct subject column.

Extend the School Lab

CREATE TABLE subjects (
  subject_code VARCHAR(10) PRIMARY KEY,
  subject_name VARCHAR(60) NOT NULL
);

CREATE TABLE results (
  student_id INT NOT NULL,
  subject_code VARCHAR(10) NOT NULL,
  marks DECIMAL(5,2) NOT NULL,
  PRIMARY KEY (student_id, subject_code),
  FOREIGN KEY (student_id)
    REFERENCES join_students(student_id),
  FOREIGN KEY (subject_code)
    REFERENCES subjects(subject_code)
);

INSERT INTO subjects VALUES
('CSC', 'Computer Science'),
('MAT', 'Mathematics');

INSERT INTO results VALUES
(1, 'MAT', 86), (1, 'CSC', 91),
(2, 'MAT', 92), (3, 'CSC', 74),
(4, 'MAT', 89);

The composite primary key prevents two result rows for the same student and subject. One student can still have many subjects.

Verified Three-Table JOIN

SELECT s.student_name,
       sub.subject_name,
       r.marks
FROM join_students AS s
JOIN results AS r
  ON r.student_id = s.student_id
JOIN subjects AS sub
  ON sub.subject_code = r.subject_code
ORDER BY s.student_id, sub.subject_code;

Five result records produce five rows. Aarav legitimately appears twice because Aarav has two subjects. Vihaan disappears because there is no result match.

Read and Build the Join Path

1. Start with the population required by the report.
2. Join the bridge or transaction table using its foreign key.
3. Join the descriptive master table using the next foreign key.
4. Qualify common columns and choose only needed output fields.
5. Predict how every one-to-many edge changes row count.

Aggregate the Joined Grain Safely

SELECT s.student_id, s.student_name,
       COUNT(r.subject_code) AS subjects_taken,
       ROUND(AVG(r.marks), 2) AS average_marks
FROM join_students AS s
JOIN results AS r
  ON r.student_id = s.student_id
GROUP BY s.student_id, s.student_name
ORDER BY s.student_id;

Define the report grain: one row per student. Adding another one-to-many table, such as attendance details, could multiply result rows and corrupt AVG or SUM. Aggregate each fact separately before joining when grains differ.

Preserve Students with No Results

SELECT s.student_name,
       COUNT(r.subject_code) AS subjects_taken,
       ROUND(AVG(r.marks), 2) AS average_marks
FROM join_students AS s
LEFT JOIN results AS r
  ON r.student_id = s.student_id
GROUP BY s.student_id, s.student_name
ORDER BY s.student_id;

COUNT of the result key returns zero for Vihaan; AVG has no non-NULL input and returns NULL. If subject names are needed too, LEFT JOIN subjects from r.subject_code so the optional chain remains optional.

Debug and Optimize

• Add one JOIN at a time and count rows after each step.
• Select both sides of each key temporarily.
• Check composite keys and unique constraints.
• Place optional-side filters in ON when the starting population must survive.
• Index foreign keys: results.student_id and results.subject_code are covered by the composite primary key only in its leftmost order; an additional subject_code index may help subject-driven access.
• Use EXPLAIN after correctness tests.

Practice: add class_name as a fourth table; calculate class averages without double-counting; list every student and only Mathematics marks while preserving students with no Mathematics result.

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.