Chapter 1 : Outer join

As a reminder, the join binds records whose foreign key matches the primary key of another table. In some cases it is however needed to have a comprehensive data set and not only when a matching occurs. That’s where outer join comes in.

Figure 1.1 : Database schema

1. Outer join

Let’s assume we’d like to get data about employees and the projects in which they are involved. In this case we’d like to get a comprehensive data set with respect to employees, namely also data about employees not involved in any projects.

SELECT emp.First_name, emp.Last_name, inv.Employee_id, inv.Project_id
FROM Employee AS emp
LEFT JOIN Involvement AS inv ON emp.ID = inv.Employee_id

We get the following table.

2. Quantification

Let’s assume we’d like get for all employees in how many projects each one is involved.

SELECT emp.First_name, emp.Last_name, COUNT( inv.Employee_id ) AS NbOfProjects
FROM Employee AS emp
LEFT JOIN Involvement AS inv ON emp.ID = inv.Employee_id
GROUP BY inv.Employee_id

We get the following table.

3. Illustration

Figure 1 2 : SQL normal join

Figure 1.3 : SQL outer join

Figure 1.2 shows that normal join put together data matching in both tables. Figure 1.3 shows that outer join in addition contains data of the table (in this case the left-hand side one) having no matching with the other table.

4. Right outer join

Right outer join is the same concept as left outer join. To illustrate this, let’s make the first example above by using a right outer join.

SELECT emp.First_name, emp.Last_name, inv.Employee_id, inv.Project_id
FROM Involvement AS inv
RIGHT JOIN Employee AS emp ON emp.ID = inv.Employee_id

We get the following table.