departments
department_id | department_name |
---|---|
26 | Physics |
28 | Mathematics |
30 | Literature |
31 | Engineering |
 
and 
studentsdepartment_id | student_name |
---|---|
26 | Robert |
28 | Mary |
30 | Frank |
35 | Joe |
 
As you can see, they both have the field department_id in common. There are two different types of joins we can perform on these tables – inner joins and outer joins. 
Inner joins
An inner join is one which only returns records in the tables that share a common value. It excludes records which are not part of the intersection of the two tables. There are two types of inner joins – explicit and implicit. The following is an example of an explicit inner join:
 
SELECT *
FROM departments
INNER JOIN students
ON departments.department_id = students.department_id;
 
The following is an example of an implicit inner join: 
SELECT *
FROM departments, students
WHERE departments.department_id = students.department_id;
 
These inner joins result in the following table:
 
department_id | department_name | department_id | student_name |
---|---|---|---|
26 | Physics | 26 | Robert |
28 | Mathematics | 28 | Mary |
30 | Literature | 30 | Frank |
 
Note that the Engineering department and Joe are both missing from this result set. This is because they do not share a common department_id with either table. Inner joins represent only the intersection of two tables; they don’t contain records outside of the intersection.
 
Outer joins, on the other hand, do include records that don’t share a common value. There are three different types of outer joins – left outer joins, right outer joins, and full outer joins.
 
A left outer join iterates over all records in the left table, in this case departments. It returns a result for every record in the departments table, even if there are no corresponding matches in the students table. Here is an example:
 
SELECT *
FROM departments
LEFT OUTER JOIN students
ON departments.department_id = students.department_id;
 
This returns the following result:
 
department_id | department_name | department_id | student_name |
---|---|---|---|
26 | Physics | 26 | Robert |
28 | Mathematics | 28 | Mary |
30 | Literature | 30 | Frank |
31 | Engineering | NULL | NULL |
 
Note that there was no match for the Engineering department in the students table, so it returned NULL for that record.
 
A right outer join is the reverse. Here’s an example:
 
SELECT *
FROM departments
RIGHT OUTER JOIN students
ON departments.department_id = students.department_id;
 
This returns the following result:
 
department_id | department_name | department_id | student_name |
---|---|---|---|
26 | Physics | 26 | Robert |
28 | Mathematics | 28 | Mary |
30 | Literature | 30 | Frank |
NULL | NULL | 35 | Joe |
 
A full outer join returns a result for every record in both tables. Here’s an example:
 
SELECT *
FROM departments
FULL OUTER JOIN students
ON departments.department_id = students_department_id;
 
department_id | department_name | department_id | student_name |
---|---|---|---|
26 | Physics | 26 | Robert |
28 | Mathematics | 28 | Mary |
30 | Literature | 30 | Frank |
31 | Engineering | NULL | NULL |
NULL | NULL | 35 | Joe |
 
That is the world of joins in a nutshell. As you can see, they are a vital element in SQL for linking the contents of one table to another.