To view all the SQL tutorials in chronological order, visit the SQL page

In this tutorial we’re going to cover Many-to-Many relationships. At this point you should already understand One-to-Many relationships, and how you can use “Foreign Keys” and “Primary Keys” to connect tables together. If not, go check out the previous tutorial.

What if you wanted to create a relationship between Students and Teachers? This could be problematic because each student has “many” teachers, and each teacher has “many” students. Which table do you put the Foreign Key in? With Many-to-Many relationships, you will need to create a third table called a “join” table. The only thing this table has is the primary key of each student and teacher that make up the relationship; The primary key of each side of the relationship.

Before I start splainin’ Let’s just get some data to play with. First, we’re going to create a “students” table and insert some “student” data into it:

CREATE TABLE students (
    student_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    gender VARCHAR(20),
    age TINYINT,
    major VARCHAR(30),
    PRIMARY KEY (student_id)
);

INSERT INTO students (student_id, name, gender, age, major)
VALUES
(1,  "Alex",     "Male",   15, "Computer Science"),
(2,  "Caroline", "Female", 34, "Liberal Arts"),
(3,  "Mary",     "Female", 19, "Nutrition"),
(4,  "Jay",      "Male",   20, "Electrical Engineering"),
(5,  "Moe",      "Male",   24, "Underwater Welding"),
(6,  "Curtis",   "Male",   18, "Psychology"),
(7,  "Judy",     "Female", 44, "Computer Science"),
(8,  "Alex",     "None ya bizness",  22, "Liberal Arts"),
(9,  "Lucy",     "Female", 23,  "Liberal Arts"),
(10, "Chris",    "Male",   56,  "Nutrition"),
(11, "Faith",    "Female", 18,  "Electrical Engineering"),
(12, "Giovanni", "Male",   30,  "Music"),
(13, "John",     "Male",   40,  "Music"),
(14, "Allison",  "Female", 20, "Performing Arts"),
(15, "Kate",     "Female", 22,  "Nutrition"),
(16, "Mary",     "None ya bizness",  26,  "Nutrition"),
(17, "Thomas",   "Male",   21,  "Computer Science"),
(18, "Owen",     "Male",   26,  "Structural Engineering"),
(19, "Sreehari", "Male",   38,  "Nutrition"),
(20, "Zena",     "Female", 25,  "Underwater Welding"),
(21, "Jason",    "Male",   39,  "Writing"),
(22, "Edwina",   "Female", 29,  "Archaeology")

Now we’ll create the “teachers” table, and insert some teacher data.

CREATE TABLE teachers (
    teacher_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    gender VARCHAR(20),
    age TINYINT,
    class VARCHAR(30),
    PRIMARY KEY (teacher_id)
);

INSERT INTO teachers (teacher_id, name, gender, age, class)
VALUES
(1, "Mrs. Troper",    "Female", 43, "Assembly Language"),
(2, "Mr. Smith",      "Male",   29, "Painting101"),
(3, "Mr. Edwards",    "Male",   47, "Calulus II"),
(4, "Mrs. Roosevelt", "Female", 51, "Music Theory"),
(5, "Mrs. Rogers",    "Female", 51, "Chemistry 201"),
(6, "Mrs. Falor",     "Female", 51, "Welding"),
(7, "Mr. Bell",       "Male",   51, "Play Writing"),
(8, "Mrs. Hawking",   "Female", 51, "Physics 101")

Now we just need to create a “students_teachers” table to show the relationship between each student and teacher. This is how we can tell which students are being taught by which teachers.

For the sake of simplicity, we’re going to do this INCORRECTLY first, that way we can see how the relationship works. At the END of the tutorial we’ll cover how to correctly setup the relationship. The difference is only minor, so no big deal.

CREATE TABLE students_teachers (
    student_id INT NOT NULL,
    teacher_id INT NOT NULL
);

INSERT INTO students_teachers ( student_id, teacher_id)
VALUES
(1, 1),
(1, 3),
(2, 5),
(2, 4),
(18, 3),
(3, 6),
(1, 2),
(5, 5),
(5, 4),
(4, 8),
(7, 8),
(8, 8),
(4, 7),
(6, 2),
(9, 3),
(9, 7),
(10, 2),
(9, 4),
(19, 4),
(5, 2),
(11, 3),
(12, 4),
(9, 2),
(14, 2),
(19, 6),
(13, 1),
(9, 8),
(16, 5),
(13, 7),
(19, 2),
(14, 6),
(16, 3),
(18, 1),
(15, 1),
(17, 4),
(16, 7),
(17, 8),
(18, 5),
(18, 7),
(19, 8)

Take a look at the three tables below. Can you see how they are connected? Can you tell which students are taking which classes, and which teachers have which students in their class just by using the information below?

Teachers

If you look at the “student_id” of the “students_teachers” table, and then find the matching “student_id” that the student_id belongs to, then you know which student the “students_teachers” table is referencing. Next, look at the “teacher_id”, and find the corresponding teacher that the “teacher_id” belongs to… That’s how you can tell which students are in which teacher’s classes!

You can even draw it out yourself. Draw a line from a row in the “students_teachers” table to the student with that “student_id”. Then draw a line from the teacher_id in the “students_teachers” table to the teacher that ID belongs to in the “teachers” table.

Try writing out which students each teacher is teaching in the tables above.

Here’s what it looks like after drawing the lines.

That’s pretty much all there is to the relationship. Now in order to pull data from the database in any meaningful way, we just have to “JOIN” these three tables together, just like we did with the One-to-Many relationships.

With Many-to-Many relationships, you have to do two joins. First, you join the “Left” side table to the “JOIN Table” (the table with the IDs), Then you join the “JOIN Table” to the “Right” table.

In our case, we’d join the students table to the JOIN table, then join the teachers table onto the join table, which connects all three together.

Let’s take this one step at a time. First, we’ll only look at the students table and the students_teachers table. let’s view the students names and student_id’s.

I’m putting a LIMIT on the query just to prevent excessive scrolling. I’m also choosing to view the “student_id” AFTER the name to make it more clear visually.

SELECT student_id, teacher_id
FROM students_teachers
ORDER BY student_id
LIMIT 10;

students_teachers table

Pay particular attention to the students_teachers table! Notice how the person with the “student_id” of 1 is in the students_teachers table THREE times. That means this student is taking THREE classes. Students with the student_id of 2, 4, and 5 are taking TWO classes, meaning they each show up twice, while student with the student_id of 3 only shows up once, meaning that student is only taking ONE class.

So when we JOIN these two tables together, you’re going to see the information as if it was one big table. We’re going to see Alex THREE times because his id is in the students_teachers table three times.

SELECT name, students.student_id, students_teachers.student_id, teacher_id
FROM students
INNER JOIN students_teachers
ON students.student_id = students_teachers.student_id
ORDER BY students_teachers.student_id
LIMIT 10;

This is almost exactly like the two tables, only they are combined into one table. We do not need the student_id twice because that’s redundant, so we’ll only include one next time.

Notice the teacher_ids hanging at the end of the table. Those are the ID’s of the teachers. Notice how it kind of looks like a table with a one-to-many relationship with the teachers. You can see that Alex has the teachers with the teacher_id of 3, 1, and 2.

All we have to do now is add ANOTHER join to get the full information. Let’s look at the two tables we’ll be joining together.

SELECT teacher_id, name, class
FROM teachers;

We’ve already done this before, so it’s no sweat to just do another JOIN! All we’re doing is using the teacher_id to connect to the teachers table so we can see all the data we need in one easy place. Note: When joining tables, remember to be specific so you don’t end up with errors about ambiguous columns. students.student_id vs student_id

SELECT students.name, students.student_id, teachers.teacher_id, teachers.name, class
FROM students
INNER JOIN students_teachers
ON students.student_id = students_teachers.student_id
INNER JOIN teachers
ON students_teachers.teacher_id = teachers.teacher_id
ORDER BY students.student_id
LIMIT 11;

Note: the name columns are still “name”, I’m just using the table prefix to make a distinction between the two “names”.

That’s all there is to it! Once you’ve joined the tables together you can query for the exact information you need just like you would in all the other queries we’ve done so far.

In the query above we organized the table to see all the classes each student is in. If you want to see it organized by teachers so you can see which students each teacher is teaching, OR if you want to see which students share the same classes, you can organize by teacher. (Or even split the table up more and organize by class).

SELECT students.name, students.student_id, teachers.teacher_id, teachers.name, class
FROM students
INNER JOIN students_teachers
ON students.student_id = students_teachers.student_id
INNER JOIN teachers
ON students_teachers.teacher_id = teachers.teacher_id
ORDER BY teachers.teacher_id
LIMIT 11;

Hopefully you get the main idea. Let’s just do a couple quick examples, and you’ll be on your way!

Let’s grab all the students in the class ‘Welding’.

SELECT students.name, class, teachers.name
FROM students
INNER JOIN students_teachers
ON students.student_id = students_teachers.student_id
INNER JOIN teachers
ON students_teachers.teacher_id = teachers.teacher_id
WHERE class = 'Welding';

Let’s try something a little more challenging: Get all the classes the students are taking for those who have some sort of “engineering” or “Computer science” Major. There’s two or three different kinds of “engineering” in the database, and “Computer Science”, so we’ll need to check for both of those. Get the students name, major, class, and teacher’s name.

SELECT students.name, major, class, teachers.name
FROM students
INNER JOIN students_teachers
ON students.student_id = students_teachers.student_id
INNER JOIN teachers
ON students_teachers.teacher_id = teachers.teacher_id
WHERE major LIKE '%engineer%' OR major LIKE '%Computer%';

Setting up the relationships properly

We’ve introduced a couple problems to our tables.

1. It’s hard to delete/update rows from the “students_teachers” table without a PRIMARY KEY. Also, the database will handle this for you if you set it up properly.

2. What if you delete a student? You now have a bunch of incorrect values in your students_teachers table. They’ll even reference students that don’t exist anymore. We need to think carefully about what the tables need to do during updates and deletes.

This is a good time to check out the Manual Page on Foreign Keys

One option is to use “no action”/”restrict” which is the default when you actually set a foreign key. With this setting, if you try to delete a student when his student_id is being used in the students_teachers table, the database will not allow you to delete that student. Instead, you’d have to first delete the rows that reference that student from the students_teachers table, THEN you can delete that student.

SET NULL Allows you to have NULL values in the foreign key columns. So if you wanted to allow students without any classes into the database, (or pets without owners) then you would want to use SET NULL.

CASCADE allows you to delete all associated columns along with the row you deleted. So if you deleted a student who was taking three classes, then those three rows in the “students_teachers” table would be deleted. (the class itself won’t be deleted, but the relationship between the student and the class will delete automatically)

If this is confusing, check out the video to watch me demonstrate

All that being said, the correct way to setup our students_teachers relationship is using ON DELETE CASCADE (there’s also ON UPDATE CASCADE) because when we delete a student, or a teacher, the relationship between the deleted person also needs to be destroyed.

We also need to identify the foreign key columns as foreign keys, and associate the foreign key with the primary key it’s referencing…. The foreign key “student_id” in “students_teachers” is just referencing the “student_id” of the students table, so we let the database know that.

So this is how you would REALLY want to create the students_teachers table:

CREATE TABLE students_teachers (
    id INT NOT NULL AUTO_INCREMENT,
    student_id INT NOT NULL,
    teacher_id INT NOT NULL,
    FOREIGN KEY (student_id)
        REFERENCES students(student_id)
        ON DELETE CASCADE,
    FOREIGN KEY (teacher_id)
        REFERENCES teachers(teacher_id)
        ON DELETE CASCADE,
    PRIMARY KEY (id)
);

Now go ahead and try to delete/update students and teachers after you re-populate the table with relationships.

Conclusion and Assignment

As you can see there’s nothing special about “Many to Many” relationships. All you’re doing is hooking tables together by a column the tables share in common. With many-to-many you’re just doing TWO joins or more.

Assignment:

You might notice that “teachers” and the “classes” probably don’t belong in the same table. If you wanted to have more information about the teachers, such as their salary, marital status, etc… or more information about the class like how many credits the class is worth, what time it starts, etc… It’d be a lot easier to have all of this information in their own tables.

Another issue you might run into is that some teachers might teach multiple classes. An English professor might teach English 101, 201, and business writing. Long story short, your goal is to break the “Class” information into its own separate table while still being able to connect students, classes, and teachers to each other.

Solution:

Note: To avoid confusion, I’m just going to start from scratch with a new database. you COULD just re-name the students_teachers table to students_classes and change the teacher_id to class_id, but I don’t want to lose anyone.

Let’s start by thinking about the relationships between the tables.

A student can take many classes, and each class can have many students. So we need a many-to-many relationship between students and classes.

A class is typically taught by only one teacher, but a teacher will usually teach multiple classes. This means we want a One-to-Many relationship between classes and teachers.

What about the relationship between Students and Teachers? The only thing that connects a teacher to a student is the class! The class will have a teacher_id, so to connect a student to a teacher, you’d have to first JOIN the student table to the class table, then from there you’ll be able to connect the teacher to the class, and then both students and teachers will be connected.

Step 0. Setup Database

Let’s create a new database for the improved version and select it for use. Call it whatever you want. I’m calling mine betterUniversity.

CREATE DATABASE betterUniversity;

Then select it for use.

Step 1. Students

Just like we did in the first portion, we need some student data. Create a “students” table, and fill it with some data. We need student_id, name, gender, age, and major as the table columns.

CREATE TABLE students (
    student_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    gender VARCHAR(20),
    age TINYINT,
    major VARCHAR(30),
    PRIMARY KEY (student_id)
);
 
INSERT INTO students (student_id, name, gender, age, major)
VALUES
(1,  "Alex",     "Male",   15, "Computer Science"),
(2,  "Caroline", "Female", 34, "Liberal Arts"),
(3,  "Mary",     "Female", 19, "Nutrition"),
(4,  "Jay",      "Male",   20, "Electrical Engineering"),
(5,  "Moe",      "Male",   24, "Underwater Welding"),
(6,  "Curtis",   "Male",   18, "Psychology"),
(7,  "Judy",     "Female", 44, "Computer Science"),
(8,  "Alex",     "None ya bizness",  22, "Liberal Arts"),
(9,  "Lucy",     "Female", 23,  "Liberal Arts"),
(10, "Chris",    "Male",   56,  "Nutrition"),
(11, "Faith",    "Female", 18,  "Electrical Engineering"),
(12, "Giovanni", "Male",   30,  "Music"),
(13, "John",     "Male",   40,  "Music"),
(14, "Allison",  "Female", 20, "Performing Arts"),
(15, "Kate",     "Female", 22,  "Nutrition"),
(16, "Mary",     "None ya bizness",  26,  "Nutrition"),
(17, "Thomas",   "Male",   21,  "Computer Science"),
(18, "Owen",     "Male",   26,  "Structural Engineering"),
(19, "Sreehari", "Male",   38,  "Nutrition"),
(20, "Zena",     "Female", 25,  "Underwater Welding"),
(21, "Jason",    "Male",   39,  "Writing"),
(22, "Edwina",   "Female", 29,  "Archaeology")

Step 2. Classes table

Instead of directly relating students to teachers, we’re relating them to classes, so we need a “classes” table. Notice the “teacher_id” column because a class can’t teach itself! It needs a teacher!

CREATE TABLE classes (
    class_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    start_time VARCHAR(20),
    credits TINYINT,
    teacher_id INT NOT NULL,
    PRIMARY KEY (class_id)
)

INSERT class Data into database

INSERT INTO classes
(name, start_time, credits, teacher_id)
VALUES
("Painting 101",      "6:30",  3, 2),
("Chemistry 201",     "13:00", 5, 5),
("Physics 101",       "9:30",  5, 8),
("Play Writing",      "10:00", 5, 7),
("Welding",           "8:00",  3, 6),
("Assembly Language", "10:30", 5, 1),
("Music Theory",      "18:00", 3, 4),
("Calculus II",       "14:30", 5, 3);

Note that this makes it easier for teachers to teach multiple classes. Just use the teacher_id in every class the teacher teaches!

Step 3. Relating Classes to Students

Now we need the join table to connect the students and classes to each other. The “Many-to-Many” relationship. You’ll notice it looks almost exactly the same as what we did at the beginning of this post because it IS the same, except we’re connecting students to classes instead of teachers.

CREATE TABLE students_classes (
    student_id INT NOT NULL,
    class_id INT NOT NULL
);
 
INSERT INTO students_classes ( student_id, class_id)
VALUES
(1, 1),
(1, 3),
(2, 5),
(2, 4),
(18, 3),
(3, 6),
(1, 2),
(5, 5),
(5, 4),
(4, 8),
(7, 8),
(8, 8),
(4, 7),
(6, 2),
(9, 3),
(9, 7),
(10, 2),
(9, 4),
(19, 4),
(5, 2),
(11, 3),
(12, 4),
(9, 2),
(14, 2),
(19, 6),
(13, 1),
(9, 8),
(16, 5),
(13, 7),
(19, 2),
(14, 6),
(16, 3),
(18, 1),
(15, 1),
(17, 4),
(16, 7),
(17, 8),
(18, 5),
(18, 7),
(19, 8)

Step 4. Adding teachers:

Now all we have left to add is the teachers! The teacher_id’s 1-8 are already in use by the classes table, so the first 8 teachers in the database have classes assigned to them already.

CREATE TABLE teachers (
    teacher_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    gender VARCHAR(20),
    age TINYINT,
    class VARCHAR(30),
    PRIMARY KEY (teacher_id)
);
 
INSERT INTO teachers (teacher_id, name, gender, age)
VALUES
(1, "Mrs. Troper",    "Female", 43),
(2, "Mr. Smith",      "Male",   29),
(3, "Mr. Edwards",    "Male",   47),
(4, "Mrs. Roosevelt", "Female", 33),
(5, "Mrs. Rogers",    "Female", 29),
(6, "Mrs. Falor",     "Female", 62),
(7, "Mr. Bell",       "Male",   49),
(8, "Mrs. Hawking",   "Female", 31)

Step 5. Testing out some queries

You can play around with this on your own. I’m just going to give the final query that connects everything together. Don’t complicate things. It’s really simple. All you do is connect tables via the primary and foreign keys.

Below, we’re connecting the students table to the students_classes via the shared column student_id. Then we’re connecting students_classes to the classes table via the shared class_id. Finally we’re connecting the teacher table to the classes table via the shared teacher_id.

SELECT students.name, classes.name, teachers.name
FROM students
INNER JOIN students_classes
ON students.student_id = students_classes.student_id
INNER JOIN classes
ON students_classes.class_id = classes.class_id
INNER JOIN teachers
ON classes.teacher_id = teachers.teacher_id
ORDER BY teachers.teacher_id
LIMIT 13;