Many-to-many with junction tables in SQL - Step-by-Step Execution

Concept Flow - Many-to-many with junction tables

Start

↓

Two main tables

↓

Create junction table

↓

Insert foreign keys from both tables

↓

Query using JOINs

↓

Get combined results

↓

End

This flow shows how two tables connect through a junction table to represent many-to-many relationships, then how queries combine data using JOINs.

Execution Sample

SQL

CREATE TABLE Students (StudentID INT, Name VARCHAR(50));
CREATE TABLE Courses (CourseID INT, Title VARCHAR(50));
CREATE TABLE Enrollments (StudentID INT, CourseID INT);
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);

This code creates two main tables and a junction table, then inserts data showing which students are enrolled in which courses.

Execution Table

Step	Action	Table Affected	Data Inserted/Queried	Result/State
1	Create Students table	Students	StudentID, Name columns	Empty Students table created
2	Create Courses table	Courses	CourseID, Title columns	Empty Courses table created
3	Create Enrollments junction table	Enrollments	StudentID, CourseID columns	Empty Enrollments table created
4	Insert students	Students	(1, 'Alice'), (2, 'Bob')	Students table has 2 rows
5	Insert courses	Courses	(101, 'Math'), (102, 'History')	Courses table has 2 rows
6	Insert enrollments	Enrollments	(1, 101), (1, 102), (2, 101)	Enrollments table has 3 rows linking students and courses
7	Query join Students and Enrollments	Query	SELECT Students.Name, Courses.Title FROM Students JOIN Enrollments ON Students.StudentID = Enrollments.StudentID JOIN Courses ON Enrollments.CourseID = Courses.CourseID	Result: Alice-Math, Alice-History, Bob-Math
8	End	-	-	All data linked and queried successfully

💡 All tables created, data inserted, and many-to-many query executed successfully

Variable Tracker

Table	Start	After Creation	After Insertions	Final State
Students	None	Empty table with columns StudentID, Name	2 rows: (1, 'Alice'), (2, 'Bob')	2 rows with student data
Courses	None	Empty table with columns CourseID, Title	2 rows: (101, 'Math'), (102, 'History')	2 rows with course data
Enrollments	None	Empty table with columns StudentID, CourseID	3 rows: (1, 101), (1, 102), (2, 101)	3 rows linking students and courses

Key Moments - 2 Insights

Why do we need a separate junction table instead of adding course IDs directly to the Students table?

Because each student can enroll in many courses and each course can have many students, a junction table allows storing multiple pairs without repeating or limiting data, as shown in execution_table step 6.

How does the JOIN in the query combine data from three tables?

The JOIN matches StudentID in Students with Enrollments, then CourseID in Enrollments with Courses, linking names and course titles together as seen in execution_table step 7.

Visual Quiz - 3 Questions

Look at the execution_table at step 6, how many enrollment records link students to courses?

A 3

B 2

C 4

D 1

💡 Hint

Check the 'Data Inserted/Queried' column in step 6 of execution_table.

At which step does the query return the combined student names and course titles?

A Step 5

B Step 3

C Step 7

D Step 4

💡 Hint

Look for the step mentioning SELECT and JOIN operations in execution_table.

If we added a new student but no enrollments, what would happen in the final query result?

A The new student appears with no courses

B The new student does not appear in the join result

C The query fails with an error

D The new student appears with all courses

💡 Hint

Recall how INNER JOIN works in SQL as shown in the query step 7.

Concept Snapshot

Many-to-many relationships use a junction table with foreign keys from both tables.
Create main tables, then a junction table.
Insert pairs of keys in junction table.
Use JOINs to combine data across tables.
This avoids data duplication and keeps relationships clear.

Full Transcript

This lesson shows how to represent many-to-many relationships in databases using junction tables. We start by creating two main tables, Students and Courses, each with their own unique IDs. Then, we create a junction table called Enrollments that holds pairs of StudentID and CourseID. We insert data into all tables, linking students to courses. Finally, we run a query joining all three tables to get a list of students and their enrolled courses. This method keeps data organized and avoids repeating information. The execution table traces each step from table creation to querying results, helping beginners see how data flows and connects.