SQL Many-to-Many JOIN — Junction Table Pattern

Many-to-Many Relationships in SQL

A many-to-many relationship exists when multiple rows in Table A can relate to multiple rows in Table B. SQL does not support this directly — you need a junction table (also called a bridge, linking, or pivot table) to connect them.

Schema Design

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE courses (
  id INT PRIMARY KEY,
  title VARCHAR(200)
);

CREATE TABLE enrollments (
  student_id INT REFERENCES students(id),
  course_id INT REFERENCES courses(id),
  enrolled_at TIMESTAMP DEFAULT NOW(),
  PRIMARY KEY (student_id, course_id)
);

The enrollments table has a composite primary key ensuring each student can enroll in a course only once.

Querying the Relationship

To get all courses for a student:

SELECT c.title, e.enrolled_at
FROM students AS s
INNER JOIN enrollments AS e ON s.id = e.student_id
INNER JOIN courses AS c ON e.course_id = c.id
WHERE s.id = 42;

Two joins are always needed: one from the entity to the junction table, another from the junction table to the related entity.

Finding Unrelated Records

"Which students are not enrolled in any course?"

SELECT s.name
FROM students AS s
LEFT JOIN enrollments AS e ON s.id = e.student_id
WHERE e.student_id IS NULL;

Aggregation

"How many students per course?"

SELECT c.title, COUNT(e.student_id) AS student_count
FROM courses AS c
LEFT JOIN enrollments AS e ON c.id = e.course_id
GROUP BY c.title
ORDER BY student_count DESC;

Extra Columns on the Junction Table

Junction tables often carry metadata about the relationship itself — enrollment date, role, score, or status. These columns describe the relationship, not either entity.

Indexing Recommendations

• Primary key on (student_id, course_id) handles lookups from student to course.
• Add a second index on (course_id, student_id) for reverse lookups (courses to students).