Multiple SQL Joins Tutorial

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.

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:

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:

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:

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.

1 Star2 Stars3 Stars4 Stars5 Stars
Loading...
Scroll Up