SQL JOIN – queries across multiple data tables

SQL JOIN is an operation in relational databases that allows queries across multiple database tables. JOINs merge data stored in different tables and output it in filtered form in a results table.

The principle of SQL JOIN is based on the relational algebra operation of the same name – a combination of Cartesian product and selection. The user determines which data from the output tables is transferred to the results table by selecting a JOIN type and defining a selection condition.

We will introduce you to the mathematical principle of SQL JOINs, compare different JOIN types, and show you how to implement JOINs in the context of database queries via SQL using practical examples.

The basic principle of SQL JOIN can be illustrated by deriving the database operation from its sub-operations. The following relational algebra operations form the basis of each JOIN:

• Cartesian product
• Selection

Please note the sequence of pair formation. For example, the Cartesian product A x B does not correspond to the same quantity as the Cartesian product B x A.

A × B = {(x,1), (x,2), (x,3), (y,1), (y,2), (y,3), (z,1), (z,2), (z,3)}

B × A = {(1,x), (1,y), (1,z), (2,x), (2,y), (2,z), (3,x), (3,y), (3,z)}

In SQL terminology, an operation in which the Cartesian product is formed from two database tables is called “cross JOIN.” In practice, cross JOINs are rarely used due to the unfiltered result set.

Selection is a relational algebra operation that allows you to select specific tuples in an initial set and output them as a result set. Which tuples are included in the result set can be determined by a comparison expression. The result of the selection is a set of tuples that fulfill the selection condition defined in the comparison expression. The Greek letter sigma (σ)is used as an operator. The operation is noted as follows:

σ_F (R)

The placeholder “F” corresponds to the relational expression, a formula of logical predicates that define the selection conditions. R stands for the dataset to be selected. Alternatively, the linear notation R[F] can be used.

The usual comparison operators are available for formulating selection conditions: for example, equal to (=), greater than (>) or less than (<).

We will explain the selection using an example that we have already introduced in the basic text of the relational database model. The following table shows fictitious personnel data that a company could have entered for its employees. The personnel number (e_id), the name (surname, first name), the social security number (ssn), the address (street, number, zip code) and the assigned company car (company car ID) are specified for each employee.

Table: employees

If we want to select the employee table so that only the employees who drive the vehicle with the vehicle_id 1 are displayed, we can proceed as follows.

σ_{vehicle_id=1}(employee)

We only retrieve the tuples where the value in the column vehicle_id is 1.

The results are shown in the following table:

Table: employee (selected)

In the SQL database language, selection conditions are defined using the “WHERE” command.

If a tuple fulfils the condition vehicle_id=1, the values of all columns should be shown for this tuple.

All common JOIN types combine the Cartesian product with a selection condition. To explain this kind of database operation, we will reduce the “employees” table to four columns for the sake of clarity. In addition, we will introduce the car table, where detailed information on the company’s vehicle fleet is stored.

Both tables are linked to each other using a foreign key relationship. The primary key of the “car” table functions as a foreign key in the “employees” table.

Table: employee

Table: car

If you want to merge the two tables and select relevant tuples at the same time, you combine the previously introduced database operations:

σ_{vehicle_id=vehicle_id}(employee × vehicle)

First, the Cartesian product is formed by employee x vehicle. The (intermin) result is a cross JOIN – a results table in which every tuple in the “employees” table is combined with every tuple in the “vehicle” table.

Table: Cartesian product “employee” × “car”

Then only the tuples are selected which show that the vehicle_id of the “vehicle” table matches the vehicle_id of the “employees” table. The selection condition is that the foreign key of the “employee” table corresponds to the primary key of the “vehicle” table.

The (final) result is a table that combines both output tables without redundancies.

Table: JOIN between “employee” and “car”

As a combination of Cartesian product and subsequent selection, JOINs combine both operations into one. The bowtie symbol (⋈) is used as an operator.

As seen here:

σ_{vehicle_id=vehicle_id}(employee × vehicle) := employee⋈_{vehicle_id=vehicle_id}vehicle

Operation σ_{vehicle_id=vehicle_id}(employee × vehicle)corresponds to a JOIN via the tables “employee” and “vehicle” with the condition vehicle_id=vehicle_id.

Transferred to the SQL syntax, the above operation would correspond to the following statement:

The inner JOIN is one of the most important JOINs used in database queries. However, special JOIN types are sometimes required to achieve the desired result.

Different SQL JOIN types are used in the relational database model, which enables queries to be executed using a group of database tables. The prerequisite for this is that the selected tables are linked to one another using foreign key relationships.

The most important JOIN types include the following:

• INNER JOINs: An inner JOIN is a filtered form of the cross JOIN, in which only the tuples of both output tables that fulfil the selection condition defined by the user are combined in the results.
• OUTER JOINs: The outer JOIN is an extension of the inner JOIN result and contains the tuples of both output tables that fulfill the selection condition defined by the user, as well as all remaining tuples from the first table, the second table, or both tables. Outer JOINs are realized as left outer JOIN, right outer JOIN, or full outer JOIN.

Regardless of the distinction between inner JOIN and outer JOIN, SQL JOINs can also be classified in the following JOIN types:

• EQUI JOIN
• NON EQUI JOIN
• SELF JOIN

Inner JOINs and outer JOINs can be implemented as equi JOINs and non equi JOINs. All JOIN examples presented so far represent equi JOINs. Equi JOINs are characterized in that they only allow the equal sign as the relational operator (=).

The selection condition of an equi JOIN is always that column values need to be equal.

In principle, however, JOINs (like selection in relational algebra) are not limited to column equality. Possible comparison operators are:

Since SQL-92, the database language with the keyword USING provides a short form for EQUI JOINS. However, this presupposes that the relevant columns have the same name, which does not necessarily have to be the case.

The following example shows two different SQL statements that lead to the same result. In the first statement, the JOIN operation is explicitly defined using the keyword ON. For the second statement, we use the short notation with the key USING.

NON EQUI JOINs on the other hand, exclude operations based on the equality of columns. All comparison operations are allowed except the equal sign (=).

Finally, a SELF JOIN is a special form of SQL JOIN in which a database table is linked to itself. In principle, any JOIN type can be executed as SELF JOIN.

If two tables are linked by columns with the same name, then it is called a NATURAL JOIN. A NATURAL JOIN is implemented by default as an INNER JOIN using the keyword with the same name. NATURAL JOINs are not set to this JOIN type. A NATURAL LEFT OUTER JOIN or a NATURAL RIGHT OUTER JOIN is also possible.

Since NATURAL JOINs are linked using columns with the same names, the respective values are not output twice in the result set, but are instead combined into a common column. Examples of NATURAL JOINs can be found in the articles on INNER JOINs and OUTER JOINs.