How to join three tables in SQL using JOINS

A basic SQL approach called joining tables allows you to join data from multiple tables based on a common column. The method is the same as joining two tables plus one extra step for joining three tables. In this article, you will learn How to join three tables using SQL joins.

Before moving to joining three tables first let's understand the types of JOINS in SQL

1. INNER JOIN: This returns records that have matching values in both tables.

2. LEFT JOIN (or LEFT OUTER JOIN): This JOIN method returns all the records from the left table and the matched records from the right table. Records from the left table that do not have a match in the right table will have NULL values in columns from the right table.

3. RIGHT JOIN (or RIGHT OUTER JOIN): This method will return all the records from the right table and the matched records from the left table. Records from the right table that do not have a match in the left table will have NULL values in columns from the left table.

4. FULL JOIN (or FULL OUTER JOIN): The FULL JOIN method will return all the records when there is a match in either the left or right table. Records that do not match in either table will have NULL values.

Now let's take an example of three tables in MYSQL and join all the three tables using JOINS method:

Table: students

id	name	father_name	age
1	Ram	Shyam	20
2	Mohan	Ramphal	25

Table: courses

id	course_name	student_id	fee
101	HTML	1	500
102	CSS	2	799

Table: class

id	class	timing	student_id
1000	BCA	9-5	2
1001	BCA	12-9	1

Here is the query to JOIN all of these tables based on student_id

SELECT * FROM students as A JOIN courses as B on A.id = B.student_id JOIN class as C on B.student_id = C.student_id WHERE A.id = 1;

Output

id	name	father_name	age	id	course_name	student_id	fee	id	class	timing	student_id
1	Ram	Shyam	20	1	HTML	1	500	2	BCA	12-9	1