SQL OUTER JOIN

Each OUTER JOIN is created as a LEFT OUTER JOIN, RIGHT OUTER JOIN or FULL OUTER JOIN.

We’ll illustrate the functionality of OUTER JOINs using the database tables “employees” and “cars”.

Table: employee

The employees table contain the first and last names of the employees of a fictitious company and the identification of the assigned company car (vehicle_id). The primary key of the table is a consistent employee ID (employee_id). The employee with ID 5 (Marshall Smith) has not yet been assigned a company car. The cell in the corresponding column therefore contains a null value.

Table: vehicle

The table “cars” contains information on the company’s vehicles: the company car make, model, registration number, year of construction, and date of next state inspection. Each company car is assigned a consistent ID (vehicle_id), which acts as the primary key of the table.

Both tables are linked by a foreign key relationship. The primary key of the “vehicle” table (the vehicle_id) was integrated into the “employees” table as a foreign key. This allows us to link both tables through a common column.

For a LEFT OUTER JOIN, the table on the left side of the JOIN operator is the dominant table. In relational algebra, LEFT OUTER JOINs are noted with the following operator: ⟕.

To link the tables “employee” and “vehicle” within a LEFT OUTER JOIN, you can use the following operation:

employee ⟕ _{vehicle_id=vehicle_id}vehicle

Interaction with the DBMS takes place in the database language SQL. The above formula corresponds to the following SQL statement:

The “employees” table is on the left side of the JOIN operator, the table “vehicle” on the right. As selection conditions, we select employee.vehicle_id=vehicle.vehicle_id. The result set of a LEFT OUTER JOIN includes all data records from the left table, and those data records from the right table that fulfill the JOIN condition. This means that only data records from the “vehicle” table are included in the JOIN result set that contained a value in the vehicle_id column, and that the DBMS can also find a corresponding value in the “employee” table for.

Missing values in the result table are output as zero values.

As a result of the LEFT OUTER JOIN we get the following table:

Table: LEFT OUTER JOIN via the “employee” and “vehicle” tables.

The result table has two special features:

The data record for vehicle_id 4 (Porsche Boxer) in the “vehicle” table does not appear in the results. The reason for this is that there is no suitable foreign key in the “employees” table for a primary key with the value 4. The selection condition is not fulfilled. The data record comes from the right output table and is therefore hidden.

The foreign key vehicle_id in the initial table “employees” contains a zero value for the data record for the employee Marshall Smith. Therefore, no corresponding primary key can be found in the “vehicle” table. The selection condition is not fulfilled here either. However, since the data record originates from the left output table, it’s still included in the result table for a LEFT JOIN. Missing values in the result table tuple are set to NULL.

The RIGHT OUTER JOIN follows the same principle as the LEFT OUTER JOIN, but the dominant table here is not the left but the right.

The result set of a RIGHT OUTER JOIN includes all tuples of the table on the right side of the JOIN operator, and the tuples of the left table that fulfill the JOIN condition. The following symbol is used as an operator: ⟖.

We’ll start again from the output tables “employees” and “cars” and define the same selection condition for the RIGHT JOIN, like the example for the LEFT JOIN.

Relational Algebra:

employee ⟖ _{vehicle_id=vehicle_id}vehicle

SQL-Statement:

The result table for the RIGHT JOIN differs significantly from the LEFT JOIN.

Table: RIGHT OUTER JOIN via the “employee” and “vehicle” tables.

The data record for employee Marshall Smith is not contained in the results table. The reason for this: the vehicle_id of the data set corresponds to the value NULL and therefore cannot be assigned to a data set on the right hand side of the table.

As a result of the RIGHT JOIN we get all data records from the “vehicle” table – as well as the data record with the vehicle_id 4, which had no employee “tuple” assigned to it.

A FULL OUTER JOIN is a combination of LEFT OUTER JOIN and RIGHT OUTER JOIN. For the operating process, the relational algebra defines the following operator is: ⟗.

We’ll also illustrate the FULL JOIN in the output tables “employees” and “vehicles” and assume the same selection conditions as before.

Relational Algebra:

Employee ⟗ _{vehicle_id=vehicle_id}vehicle

SQL Statement:

The result corresponds to the following table:

Table: FULL OUTER JOIN for the tables “employees” and “vehicles”.

The FULL JOIN connects the output table’s data records according to selection conditions. It doesn’t just list the linked data records in the result table, but also links the data records of both tables that do not fulfill the selection condition.

Missing values are also set to NULL for FULL JOIN.

Like INNER JOINs, OUTER JOINs can also be implemented as NATURAL JOINs. The corresponding operators are:

Or:

NATURAL OUTER JOINs connect tables using columns with the same names. Which columns are selected can be explicitly defined using the USING keyword:

Alternatively, you can use a short notation whereby the DBMS automatically searches for columns with the same names and connects the listed tables through them:

With reference to the example tables listed above, both SQL statements lead to the same result.

In NATURAL LEFT JOIN, the columns employee.vehicle_id and vehicle.vehicle_id are merged into the common column vehicle_id.

OUTER JOINS usually lead to table groups with zero values. This is useful, for example, if you want to highlight them. In our example, the Porsche Boxster has not yet been assigned to an employee. This is not shown in the “vehicle” table. And the “employees” table merely shows that Marshall Smith has not yet used a company car. After a FULL JOIN over both tables, it is easy to see that Marshall could drive the Porsche in the future.