Hi, everyone! Today we are going to talk about something that causes a certain degree of confusion among those who are new to SQL: the different types of JOIN.
To illustrate, we are going to use the following database schema:
with the following data:
1. Full Join
It brings the results in both the right join and in the left join, as long as they satisfy the join condition:
SELECT * FROM classes c
FULL JOIN teachers t ON t.teacher_id = c.teacher_id
The result is composed by the classes being given by a certain teacher, the classes without a teacher and the teachers not giving any classes
2. Inner Join
It brings all the records from the two tables, as long as the join condition is satisfied.
SELECT * FROM classes s
INNER JOIN teachers t ON t.teacher_id = c.teacher_id
The result is composed by the classes being given by a certain teacher
3. Left Join
It brings the records in the right table that satisfy the join condition and all the records in the left table
SELECT * FROM classes c
LEFT JOIN teachers t ON t.teacher_id = c.teacher_id
The result is composed by the classes being given by a certain teacher and the classes without a teacher.
4. Right Join
It brings the records in the left table that satisfy the join condition and all the records in the right table
SELECT * FROM classes c
RIGHT JOIN teachers t ON t.teacher_id = c.teacher_id
The result is composed by the classes being given by a certain teacher and the teachers not giving any classes.
5. Cross Join
It brings all the combinations of records of the two tables
SELECT * FROM teachers
CROSS JOIN classes
6. Queries with more than one JOIN
You can combine how many joins you like in a query. For example, if you want to know the classes that a student is attend you can write something like this:
SELECT s.name, c.name
FROM students s INNER JOIN classes_students cs ON s.student_id = cs.student_id
INNER JOIN classes c ON c.class_id = cs.class_id
7. Now, it is your turn!
Can you write a query to list the name of the students having class with a certain teacher?
I hope you enjoyed this post!