Tell me about a many:many relationship

SQLJunior~1 min read

A many-to-many (M:N) relationship is when each record in one table can relate to several records in another, and vice versa, each record in the second table can relate to several in the first.

This kind of relationship can't be implemented directly - it requires a junction (linking) table.

Example: students and courses

One student can enroll in several courses, and one course can be taken by several students.

Students table:

sql

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

Courses table:

sql

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

Junction table:

sql

CREATE TABLE student_courses (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

The student_courses table stores pairs of "which student, which course." This creates a two-way relationship: many students <-> many courses.

How it looks logically

students	courses	student_courses
1, Anna	10, SQL	(1, 10)
2, Ivan	11, Python	(1, 11)
		(2, 10)

Anna takes SQL and Python, and the SQL course is taken by Anna and Ivan.

Example query

To get all students and the courses they're enrolled in:

sql

SELECT 
  students.name, 
  courses.title
FROM student_courses
JOIN students ON student_courses.student_id = students.id
JOIN courses ON student_courses.course_id = courses.id;

Where M:N relationships are used

Students <-> Courses
Authors <-> Books
Users <-> Roles
Doctors <-> Patients
Products <-> Categories

Details

It's implemented via a junction table holding two foreign keys.
That table's primary key is usually composite ((id1, id2)).
The junction table often stores extra data too, such as an enrollment date.

Summary: A many-to-many relationship connects objects that can relate both ways to many elements. It's implemented via a third table that links the two main ones.

Short Answer

Interview ready

Premium

A concise answer to help you respond confidently on this topic during an interview.