Many-to-many with junction tables in SQL

Introduction

Many-to-many relationships let us connect items from two groups where each item can link to many from the other group. Junction tables help organize these links clearly.

When students can enroll in many courses, and courses have many students.

When books can have multiple authors, and authors write multiple books.

When customers can buy many products, and products can be bought by many customers.

When movies have many actors, and actors act in many movies.

Syntax

SQL

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

CREATE TABLE TableB (
  id INT PRIMARY KEY,
  description VARCHAR(100)
);

CREATE TABLE JunctionTable (
  tableA_id INT,
  tableB_id INT,
  PRIMARY KEY (tableA_id, tableB_id),
  FOREIGN KEY (tableA_id) REFERENCES TableA(id),
  FOREIGN KEY (tableB_id) REFERENCES TableB(id)
);

The junction table holds pairs of IDs from the two tables to link them.

Primary key on both columns prevents duplicate links.

Examples

This example shows students and courses linked by enrollments.

SQL

CREATE TABLE Students (
  student_id INT PRIMARY KEY,
  student_name VARCHAR(50)
);

CREATE TABLE Courses (
  course_id INT PRIMARY KEY,
  course_name VARCHAR(50)
);

CREATE TABLE Enrollments (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES Students(student_id),
  FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

This example links authors and books they wrote.

SQL

CREATE TABLE Authors (
  author_id INT PRIMARY KEY,
  author_name VARCHAR(50)
);

CREATE TABLE Books (
  book_id INT PRIMARY KEY,
  book_title VARCHAR(100)
);

CREATE TABLE AuthorBook (
  author_id INT,
  book_id INT,
  PRIMARY KEY (author_id, book_id),
  FOREIGN KEY (author_id) REFERENCES Authors(author_id),
  FOREIGN KEY (book_id) REFERENCES Books(book_id)
);

Sample Program

This query shows which students are enrolled in which courses using the junction table.

SQL

CREATE TABLE Students (
  student_id INT PRIMARY KEY,
  student_name VARCHAR(50)
);

CREATE TABLE Courses (
  course_id INT PRIMARY KEY,
  course_name VARCHAR(50)
);

CREATE TABLE Enrollments (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES Students(student_id),
  FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

INSERT INTO Students VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO Courses VALUES (101, 'Math'), (102, 'History');
INSERT INTO Enrollments VALUES (1, 101), (1, 102), (2, 101);

SELECT s.student_name, c.course_name
FROM Enrollments e
JOIN Students s ON e.student_id = s.student_id
JOIN Courses c ON e.course_id = c.course_id
ORDER BY s.student_name, c.course_name;

Important Notes

Always use foreign keys in the junction table to keep data linked correctly.

Composite primary key in the junction table avoids duplicate pairs.

You can add extra columns in the junction table for details about the relationship, like enrollment date.

Summary

Many-to-many relationships connect items from two tables in pairs.

Junction tables store these pairs with foreign keys to both tables.

This setup keeps data organized and easy to query.