SQL OUTER JOIN

An SQL JOIN is a query operation that links several tables in a re­la­tion­al database and outputs their data records (tuples) filtered according to a selection condition defined by the user.

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

We’ll il­lus­trate the func­tion­al­ity 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 fic­ti­tious company and the iden­ti­fic­a­tion of the assigned company car (vehicle_id). The primary key of the table is a con­sist­ent employee ID (employee_id). The employee with ID 5 (Marshall Smith) has not yet been assigned a company car. The cell in the cor­res­pond­ing column therefore contains a null value.

Table: vehicle

The table “cars” contains in­form­a­tion on the company’s vehicles: the company car make, model, re­gis­tra­tion number, year of con­struc­tion, and date of next state in­spec­tion. Each company car is assigned a con­sist­ent ID (vehicle_id), which acts as the primary key of the table.

Both tables are linked by a foreign key re­la­tion­ship. The primary key of the “vehicle” table (the vehicle_id) was in­teg­rated 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 re­la­tion­al 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

In­ter­ac­tion with the DBMS takes place in the database language SQL. The above formula cor­res­ponds 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 con­di­tions, 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 cor­res­pond­ing 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 cor­res­pond­ing primary key can be found in the “vehicle” table. The selection condition is not fulfilled here either. However, since the data record ori­gin­ates 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.

Re­la­tion­al Algebra:

employee ⟖ _{vehicle_id=vehicle_id}vehicle

SQL-Statement:

The result table for the RIGHT JOIN differs sig­ni­fic­antly 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 cor­res­ponds 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 com­bin­a­tion of LEFT OUTER JOIN and RIGHT OUTER JOIN. For the operating process, the re­la­tion­al algebra defines the following operator is: ⟗.

We’ll also il­lus­trate the FULL JOIN in the output tables “employees” and “vehicles” and assume the same selection con­di­tions as before.

Re­la­tion­al Algebra:

Employee ⟗ _{vehicle_id=vehicle_id}vehicle

SQL Statement:

The result cor­res­ponds 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 con­di­tions. 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 im­ple­men­ted as NATURAL JOINs. The cor­res­pond­ing operators are:

Or:

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

Al­tern­at­ively, you can use a short notation whereby the DBMS auto­mat­ic­ally searches for columns with the same names and connects the listed tables through them:

With reference to the example tables listed above, both SQL state­ments 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.