I’ll explain how to join more than two tables in SQL.
But first of all, you need to be sure that your MySQL server is installed and running.
Let’s create 3 table and write a join SQL statement.
For example, we have a student table with 3 students “John”, “Henry” and “Michael”.
| id | name |
| 1 | John |
| 2 | Henry |
| 3 | Michael |
And there are 5 available courses in the school: “Maths”, “Physics”, “Biology”, “Chemistry” and “History”.
| id | name |
| 1 | Maths |
| 2 | Physics |
| 3 | Biology |
| 4 | Chemistry |
| 5 | History |
And we need to create a table to store references between students and courses that contains student id and course id.
| student_id | course_id |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 2 |
| 2 | 3 |
| 2 | 4 |
There are 2 types of joins in the MySQL: inner join and outer join.
The difference is outer join keeps nullable values and inner join filters it out.
So I’ll show you examples of joining 3 tables in MySQL for both types of join.
How To Inner Join Multiple Tables
I want to select all students and their courses.
So we need to write MySQL query to take the data from multiple tables.
That’s an example how to join 3 tables in MySQL.
SELECT s.name AS student_name, c.name AS course_name FROM student s
INNER JOIN student_course sc ON s.id = sc.student_id
INNER JOIN course c ON sc.course_id = c.id;Output:
| student_name | course_name |
| John | Maths |
| John | Physics |
| John | Biology |
| Henry | Physics |
| Henry | Biology |
| Henry | Chemistry |
As you can see we executed inner join for three tables and retrieved 2 columns: student.name and course.name.
Note that result doesn’t contain nullable values.
You can join 4 or even more SQL tables in the same way.
If you want to filter the result by student name, for example, you can add WHERE clause at the end:
SELECT s.name AS student_name, c.name AS course_name FROM student s
INNER JOIN student_course sc ON s.id = sc.student_id
INNER JOIN course c ON sc.course_id = c.id
WHERE s.name = 'John';How To Outer Join Multiple Tables
Another way to join 3 tables is to use an outer join.
The outer join can be 2 types: left join and right join.
This is an example of three table left join:
SELECT s.name AS student_name, c.name AS course_name FROM student s
LEFT JOIN student_course sc ON s.id = sc.student_id
LEFT JOIN course c ON sc.course_id = c.id;Output:
| student_name | course_name |
| John | Maths |
| John | Physics |
| John | Biology |
| Henry | Physics |
| Henry | Biology |
| Henry | Chemistry |
| Michael | NULL |
As you can see Michael didn’t take any course, that’s why course_name is NULL for him.
It’s a difference with inner joins.
That’s an example of multi-table right join SQL:
SELECT s.name AS student_name, c.name AS course_name FROM student s
RIGHT JOIN student_course sc ON s.id = sc.student_id
RIGHT JOIN course c ON sc.course_id = c.id;Output:
| student_name | course_name |
| John | Maths |
| John | Physics |
| Henry | Physics |
| John | Biology |
| Henry | Biology |
| Henry | Chemistry |
| NULL | History |
As you can see query started joining tables from another side.
Now “History” course has a NULL value of student_name.
Do you have any question? Please, ask me.