Types of JOIN Query

If the JOIN keyword is left out, SQL performs an INNER JOIN by default. That is, for each hooked up primary key-foreign key pair, it brings up only the rows of information that match conditions in both tables. A LEFT JOIN brings up all the rows of the table on the left hand side of the LEFT JOIN keyword and only rows that where both fields are equal from the table on the right, while a RIGHT JOIN brings up all the rows of the table on the right hand side of the RIGHT JOIN keyword and only rows where both fields are equal from the table on the left. Let us use an example outside our database to understand the differences between the three types of joins.

Table GymTeachers :

Teacher_ID	Name
01	Sample Teacher 1
02	Sample Teacher 2
03	Sample Teacher 3
04	Sample Teacher 4

Table ServiceTeachers:

Service_ID	ServiceName	Teacher_ID
1	Cardio	04
2	Weights	04
3	Aerobics	02
4	Massage

Join Types

INNER

SELECT GymTeachers.Name, ServiceTeachers.ServiceName
FROM GymTeachers, ServiceTeachers
WHERE GymTeachers.Teacher_ID = ServiceTeachers.TeacherID

Output:

Name	ServiceName
Sample Teacher 4	Cardio
Sample Teacher 4	Weights
Sample Teacher 2	Aerobics

 

LEFT

SELECT GymTeachers.Name, ServiceTeachers.ServiceName
FROM GymTeachers LEFT JOIN ServiceTeachers
WHERE GymTeachers.Teacher_ID = ServiceTeachers.TeacherID

Output:

Name	Product
Sample Teacher 1
Sample Teacher 2	Aerobics
Sample Teacher 3
Sample Teacher 4	Cardio
Sample Teacher 4	Weights

 

RIGHT

SELECT GymTeachers.Name, ServiceTeachers.ServiceName
FROM GymTeachers RIGHT JOIN ServiceTeachers
WHERE GymTeachers.Teacher_ID = ServiceTeachers.TeacherID

Output:

Name	Product
Sample Teacher 2	Aerobics
Sample Teacher 4	Cardio
Sample Teacher 4	Weights
	Massage