MySQL Many to Many: Junction Table Design and Queries

In a many-to-many relationship, rows on both sides can be linked to many rows on the other: students and courses, posts and tags, products and orders.

A single foreign key cannot express it. The solution is a third table, the junction table (also called a link, bridge or association table). It holds one row per pair.

query.sql

SQL

CREATE TABLE students (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(40) NOT NULL
);
CREATE TABLE courses (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(60) NOT NULL
);

CREATE TABLE enrollments (
    student_id INT UNSIGNED NOT NULL,
    course_id INT UNSIGNED NOT NULL,
    enrolled_on DATE NOT NULL DEFAULT (CURRENT_DATE),
    grade CHAR(1) NULL,
    PRIMARY KEY (student_id, course_id),
    KEY idx_course (course_id),
    CONSTRAINT fk_enr_student FOREIGN KEY (student_id) REFERENCES students (id) ON DELETE CASCADE,
    CONSTRAINT fk_enr_course  FOREIGN KEY (course_id)  REFERENCES courses (id)  ON DELETE CASCADE
);

INSERT INTO students (name) VALUES ('Asha'), ('Ben'), ('Chitra');
INSERT INTO courses (title) VALUES ('SQL Basics'), ('PHP Fundamentals'), ('Web Design');
INSERT INTO enrollments (student_id, course_id, enrolled_on, grade) VALUES
    (1, 1, '2026-01-10', 'A'), (1, 2, '2026-01-10', 'B'), (2, 1, '2026-01-12', NULL), (3, 1, '2026-02-01', 'A'), (3, 3, '2026-02-01', NULL);

The design

Two foreign keys: one to each side.
The composite primary key (student_id, course_id) guarantees that a student cannot enrol in the same course twice, and it needs no extra id column.
A second index on course_id helps queries that start from the course side. (The primary key already serves the other direction.)
The junction table can carry its own data: here the enrolment date and the grade. These facts belong to the pair, not to the student or the course alone.

Query in both directions

Courses of one student:

query.sql

SQL

SELECT s.name AS student, c.title AS course, e.grade
FROM students s
JOIN enrollments e ON e.student_id = s.id
JOIN courses c ON c.id = e.course_id
WHERE s.name = 'Asha'
ORDER BY c.title;

Output

OUTPUT

+---------+------------------+-------+
| student | course           | grade |
+---------+------------------+-------+
| Asha    | PHP Fundamentals | B     |
| Asha    | SQL Basics       | A     |
+---------+------------------+-------+
2 rows in set

Students of one course:

query.sql

SQL

SELECT c.title AS course, s.name AS student
FROM courses c
JOIN enrollments e ON e.course_id = c.id
JOIN students s ON s.id = e.student_id
WHERE c.title = 'SQL Basics'
ORDER BY s.name;

Output

OUTPUT

+------------+---------+
| course     | student |
+------------+---------+
| SQL Basics | Asha    |
| SQL Basics | Ben     |
| SQL Basics | Chitra  |
+------------+---------+
3 rows in set

Counting

query.sql

SQL

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

Output

OUTPUT

+------------------+----------+
| title            | students |
+------------------+----------+
| SQL Basics       |        3 |
| PHP Fundamentals |        1 |
| Web Design       |        1 |
+------------------+----------+
3 rows in set

Finding the non-existing pairs

Which courses has Ben not taken?

query.sql

SQL

SELECT c.title
FROM courses c
WHERE NOT EXISTS (SELECT 1 FROM enrollments e WHERE e.course_id = c.id AND e.student_id = 2)
ORDER BY c.title;

Output

OUTPUT

+------------------+
| title            |
+------------------+
| PHP Fundamentals |
| Web Design       |
+------------------+
2 rows in set

Duplicate protection

query.sql

SQL

INSERT INTO enrollments (student_id, course_id) VALUES (1, 1);

Output

OUTPUT

ERROR 1062 (23000): Duplicate entry '1-1' for key 'enrollments.PRIMARY'

The primary key refuses a second enrolment of the same pair.

Another example: tags

Posts and tags follow the same shape:

query.sql

SQL

CREATE TABLE posts (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(40) NOT NULL);
CREATE TABLE tags  (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL UNIQUE);
CREATE TABLE post_tags (
    post_id INT UNSIGNED NOT NULL,
    tag_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    KEY idx_tag (tag_id),
    FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags (id) ON DELETE CASCADE
);
INSERT INTO posts (title) VALUES ('Intro to SQL'), ('Faster queries');
INSERT INTO tags (name) VALUES ('sql'), ('mysql'), ('performance');
INSERT INTO post_tags VALUES (1, 1), (1, 2), (2, 2), (2, 3);

SELECT p.title, GROUP_CONCAT(t.name ORDER BY t.name) AS tags
FROM posts p
JOIN post_tags pt ON pt.post_id = p.id
JOIN tags t ON t.id = pt.tag_id
GROUP BY p.id, p.title
ORDER BY p.id;

Output

OUTPUT

+----------------+-------------------+
| title          | tags              |
+----------------+-------------------+
| Intro to SQL   | mysql,sql         |
| Faster queries | mysql,performance |
+----------------+-------------------+
2 rows in set

Posts with all of a set of tags (for example both sql and mysql):

query.sql

SQL

SELECT p.title
FROM posts p
JOIN post_tags pt ON pt.post_id = p.id
JOIN tags t ON t.id = pt.tag_id AND t.name IN ('sql', 'mysql')
GROUP BY p.id, p.title
HAVING COUNT(DISTINCT t.id) = 2;

Output

OUTPUT

+--------------+
| title        |
+--------------+
| Intro to SQL |
+--------------+
1 row in set

Choosing the primary key of the junction table

Option	Use when
Composite key of the two foreign keys	The pair can exist once (enrolments, tags)
A surrogate `id` + `UNIQUE (a, b)`	You must reference a single link row from other tables, or ORMs require a single-column key
A surrogate `id`, no uniqueness	The same pair can repeat (several purchases of the same product by the same person, or events)

Do not do this

Do not store a comma-separated list of ids in a column (courses = '1,2,3'). It cannot be indexed, cannot be checked by a foreign key and makes every query painful (Splitting Text).

lightbulb

Name it by what it means

enrollments and order_items tell the story. student_course works, but a name that reflects the real-world event is clearer, and invites you to add the columns that belong to it.