Chapter 9: Joins
Joins are what make relational databases magical. They let us connect different tables using common columns (usually primary key and foreign key) and combine data from multiple tables in one beautiful query.
Today we’ll learn:
- INNER JOIN – Only matching rows
- LEFT JOIN / RIGHT JOIN – Keep all from one side
- FULL OUTER JOIN – (MySQL doesn’t have it directly, but we emulate it)
- SELF JOIN – Join a table with itself
- CROSS JOIN – Cartesian product (every row with every row)
- Multiple table joins – Joining 3+ tables
First – let’s create two related tables with sample data
We’ll use our coaching_class database and create two tables:
- students (we already have it)
- courses (new table)
- enrollments (junction table that connects students and courses)
Run these commands to set up the data:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
-- Create courses table CREATE TABLE courses ( course_id INT AUTO_INCREMENT PRIMARY KEY, course_name VARCHAR(100) NOT NULL, fees DECIMAL(10,2) NOT NULL ); -- Insert some courses INSERT INTO courses (course_name, fees) VALUES ('Mathematics', 6000.00), ('Science', 5500.00), ('English', 4500.00), ('Hindi', 4000.00), ('Computer Science', 8000.00); -- Create enrollments table (junction table) CREATE TABLE enrollments ( enrollment_id INT AUTO_INCREMENT PRIMARY KEY, student_id INT NOT NULL, course_id INT NOT NULL, enrollment_date DATE DEFAULT CURRENT_DATE, FOREIGN KEY (student_id) REFERENCES students(student_id), FOREIGN KEY (course_id) REFERENCES courses(course_id) ); -- Insert some enrollments INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES (1, 1, '2025-01-06'), -- Priya → Mathematics (1, 2, '2025-01-06'), -- Priya → Science (2, 3, '2025-01-11'), -- Rahul → English (3, 1, '2025-02-16'), -- Sneha → Mathematics (4, 5, '2025-03-02'), -- Aarav → Computer Science (5, 4, '2025-03-11'), -- Kavya → Hindi (7, 2, '2025-03-01'), -- Ananya → Science (8, 1, '2025-02-01'); -- Vikram → Mathematics |
Now we have:
- 10 students
- 5 courses
- 8 enrollments (some students enrolled in multiple courses, some courses have multiple students)
1. INNER JOIN – Only Matching Rows from Both Tables
INNER JOIN returns only the rows where there is a match in both tables.
Syntax:
|
0 1 2 3 4 5 6 7 8 |
SELECT columns FROM table1 INNER JOIN table2 ON table1.common_column = table2.common_column; |
Example – Show students with their enrolled courses
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT s.full_name AS 'Student Name', c.course_name AS 'Course Enrolled', e.enrollment_date FROM students s INNER JOIN enrollments e ON s.student_id = e.student_id INNER JOIN courses c ON e.course_id = c.course_id ORDER BY s.full_name, c.course_name; |
Result: Only the 8 students who have enrolled in at least one course will appear (Rohan Mehta, Meera Desai, Arjun Reddy are not shown because they have no enrollments).
2. LEFT JOIN – Keep ALL Rows from Left Table
LEFT JOIN (or LEFT OUTER JOIN) keeps all rows from the left table, and matches from the right table. If no match → NULL in right columns.
Example – Show ALL students and their courses (even those with no courses)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT s.full_name AS 'Student Name', c.course_name AS 'Course Enrolled', e.enrollment_date FROM students s LEFT JOIN enrollments e ON s.student_id = e.student_id LEFT JOIN courses c ON e.course_id = c.course_id ORDER BY s.full_name, c.course_name; |
Result: All 10 students appear. Students with no enrollments will show NULL in course_name and enrollment_date.
3. RIGHT JOIN – Keep ALL Rows from Right Table
RIGHT JOIN keeps all rows from the right table, and matches from the left.
Example – Show ALL courses and the students enrolled in them (even courses with no students)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT c.course_name AS 'Course Name', s.full_name AS 'Student Name' FROM students s RIGHT JOIN enrollments e ON s.student_id = e.student_id RIGHT JOIN courses c ON e.course_id = c.course_id ORDER BY c.course_name; |
Result: All 5 courses appear. “Hindi” has Kavya, “Computer Science” has Aarav, etc. If a course had no enrollments (none in our data), it would still show with NULL student.
4. FULL OUTER JOIN – (Emulated in MySQL)
MySQL does not have FULL OUTER JOIN directly, but we can emulate it using LEFT JOIN + RIGHT JOIN + UNION.
Emulated FULL OUTER JOIN:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SELECT s.full_name, c.course_name FROM students s LEFT JOIN enrollments e ON s.student_id = e.student_id LEFT JOIN courses c ON e.course_id = c.course_id UNION SELECT s.full_name, c.course_name FROM students s RIGHT JOIN enrollments e ON s.student_id = e.student_id RIGHT JOIN courses c ON e.course_id = c.course_id WHERE s.student_id IS NULL; |
This shows all students + all courses, with NULLs where there is no match.
5. SELF JOIN – Join a Table with Itself
Useful when a table has a hierarchical or comparative relationship.
Example – Find students who share the same birth month
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT s1.full_name AS 'Student 1', s2.full_name AS 'Student 2', MONTH(s1.date_of_birth) AS 'Birth Month' FROM students s1 INNER JOIN students s2 ON s1.student_id < s2.student_id -- avoid duplicates WHERE MONTH(s1.date_of_birth) = MONTH(s2.date_of_birth) ORDER BY MONTH(s1.date_of_birth); |
Result: Pairs of students born in the same month (e.g., Priya and Meera both in February).
6. CROSS JOIN – Cartesian Product
CROSS JOIN combines every row from first table with every row from second table (no ON condition needed).
Syntax:
|
0 1 2 3 4 5 6 |
SELECT * FROM table1 CROSS JOIN table2; |
Example – All possible student-course combinations (even if not enrolled)
|
0 1 2 3 4 5 6 7 8 9 10 11 |
SELECT s.full_name, c.course_name FROM students s CROSS JOIN courses c LIMIT 10; -- just to not show 10 students × 5 courses = 50 rows |
Result: 50 rows! Every student paired with every course.
When to use: Rare, but useful for generating combinations or testing.
7. Multiple Table Joins – Joining 3+ Tables
We already did this in INNER JOIN example — it’s just chaining more JOINs.
Example – Full report: Student name, course name, fees, enrollment date
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT s.full_name AS 'Student', c.course_name AS 'Course', c.fees AS 'Fees', e.enrollment_date AS 'Enrolled On' FROM students s LEFT JOIN enrollments e ON s.student_id = e.student_id LEFT JOIN courses c ON e.course_id = c.course_id WHERE s.is_active = TRUE ORDER BY s.full_name; |
That’s it for Chapter 9! 🎉 You can now connect any related tables and answer complex business questions!
Homework for today (do it right now – very important!) Run these 4 queries and paste the output (at least first 5–6 rows of each):
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
-- 1. INNER JOIN: Students and their courses SELECT s.full_name, c.course_name FROM students s INNER JOIN enrollments e ON s.student_id = e.student_id INNER JOIN courses c ON e.course_id = c.course_id ORDER BY s.full_name; -- 2. LEFT JOIN: ALL students and their courses (even without) SELECT s.full_name, c.course_name FROM students s LEFT JOIN enrollments e ON s.student_id = e.student_id LEFT JOIN courses c ON e.course_id = c.course_id ORDER BY s.full_name; -- 3. Students who are enrolled in Mathematics SELECT s.full_name, c.course_name FROM students s INNER JOIN enrollments e ON s.student_id = e.student_id INNER JOIN courses c ON e.course_id = c.course_id WHERE c.course_name = 'Mathematics'; -- 4. Courses with number of students enrolled (GROUP BY + JOIN) SELECT c.course_name, COUNT(e.student_id) AS 'Enrolled Students' FROM courses c LEFT JOIN enrollments e ON c.course_id = e.course_id GROUP BY c.course_name ORDER BY 'Enrolled Students' DESC; |
