Skip to content
Back to DatabasesAll lessons

Databases

Many-to-Many (a join table in the middle)

O(n × m) with a simple nested-loop match through the join table twice. O(n + m) with hash indexes on both foreign keys — the same idea as a regular two-table join.

The idea, in plain English

A student can take many courses, and a course can have many students. Neither side can hold just one foreign key, the way an order holds one user_id. The fix is a third table in the middle, like a class roster clipboard. It lists nothing but pairs: (student_id, course_id). Each row on that roster links exactly one student to exactly one course they are taking.

How it works

  1. 1Keep three tables: students, courses, and a join table, often called 'enrollments'. The join table holds just two foreign keys, student_id and course_id, with one row per pairing.
  2. 2To answer 'which courses does Ana take?', join students to enrollments by matching student_id, then join that result to courses by matching course_id.
  3. 3Adding or removing a pairing means adding or removing exactly one row in the join table. The students and courses tables themselves never have to change.

When you'd use it

Use a join table for any real many-to-many relationship: students and courses, actors and movies, tags and posts, users and roles. Use it anywhere each side can have several of the other side.

Common beginner mistakes

  • Do not cram multiple course IDs into one column on the students table, such as a comma-separated list, instead of using a proper join table. This is a classic beginner mistake, and it breaks ordinary filtering and joining.
  • A join-table row only means something as a pair. A row with just a student_id, or just a course_id, does not represent a real enrollment.

Try it — edit and run

Click the code to edit · press ⌘/Ctrl+↵ to run

Editable code. Tab and Shift+Tab indent. Press Escape, then Tab, to move focus out of the editor.

Expected output — hit Run to try it
SQL: SELECT students.name, courses.title FROM students JOIN enrollments ON students.id = enrollments.student_id JOIN courses ON enrollments.course_id = courses.id
Ana Math
Ana History
Bilal History
Cara Art
Cara Math
Courses per student:
Ana: History, Math
Bilal: History
Cara: Art, Math

Not sure this is the right topic? See the learning paths → or where this leads →