INNER JOIN: defin­i­tion and ap­plic­a­tion

Multiple database tables in the re­la­tion­al database model can be queried using SQL-JOINs. With the exception of the CROSS-JOIN, all JOIN types are a com­bin­a­tion of Cartesian product and selection.

The database man­age­ment system (DBMS) initially forms the cross product of two database tables. It then filters the result according to a selection condition defined by the user using an SQL statement. The INNER JOIN differs from all other JOIN types by its minimal result set. Only the data records of a cross product that fulfills the selection con­di­tions are output as the result of an INNER JOIN. The result is a result table (view) without zero values.

We are il­lus­trat­ing the INNER JOIN using an example here, and are assuming two tables. The table “employees” contains the employees of a company including the employee ID (e_id) and the cor­res­pond­ing de­part­ment (d_id).

Table: employees

The table shows two special features: the employees Muller and McClain work in the same de­part­ment. The employee Connolly has not yet been assigned to a de­part­ment.

The table “de­part­ments” lists all of the company’s de­part­ments including ID and location.

Table: de­part­ments

Both tables are linked by a foreign key re­la­tion­ship. The de­part­ment ID, which is the primary key in the “de­part­ments” table, has been in­teg­rated into the “employees” table as a foreign key.

The link allows us to use an INNER JOIN over both tables. This is necessary, for example, to determine which employee works at which location.

When querying re­la­tion­al databases, a match of primary and foreign keys is usually defined as a selection condition. The condition is con­sidered fulfilled if the selected foreign key of one table matches the primary key of the other table (=). This means that only those data records that contain common values are output.

An INNER JOIN like this is noted in re­la­tion­al algebra as follows:

employee⋈_{d_id=d_id}de­part­ments

However, re­la­tion­al database systems do not accept commands in re­la­tion­al algebra syntax, but in the form of SQL state­ments.

The SELECT command instructs the DBMS to query data from the database. Al­tern­at­ively, SQL offers the option of entering (INSERT INTO), changing (UPDATE) or deleting (DELETE FROM) data. The SELECT command is followed by a spe­cific­a­tion of what data needs to be retrieved. Since we want to retrieve the complete data set, we are choosing an ap­pro­pri­ate place­hold­er: the asterisk (*).

The SELECT command always requires the keyword FROM and the spe­cific­a­tion from which table or table group (JOIN) the data is to be retrieved. In our case, the data source is an INNER JOIN via the “de­part­ments” and “employees” tables. We also specify a condition for the link with the keyword ON. We just want to link the data records and output them as a result table for which the “d_id” of the “employees” table cor­res­ponds to the “d_id” of the “de­part­ments” table.

An INNER JOIN through the two output tables with the condition “employee.d_id=de­part­ments.d_id” returns the following result table:

Table: SQL INNER JOIN between “employee” and “de­part­ments”

If you compare the result table with the two output tables, you notice that one data record is missing from each table: the data records whose value in column “d_id” has no cor­res­pond­ence in the other table.

and

INNER JOINS can be cat­egor­ised as THETA JOINs, EQUI JOINs, NON EQUI JOINs, and NATURAL JOINs.

The INNER JOIN in SQL ter­min­o­logy cor­res­ponds to the THETA JOIN of re­la­tion­al algebra. The THETA JOIN differs from EQUI JOINs and NON EQUI JOINs in that it provides users with an unlimited set of com­par­is­on operators. EQUI JOINs, on the other hand, restrict the selection condition for queries to the equality of column values. For NON EQUI JOINs, all re­la­tion­al operators except the equals sign are allowed.

If two tables (like in the previous examples) are connected using columns with the same names, INNER JOINs are usually converted as NATURAL JOINs.

NATURAL JOINs are sub­spe­cies of EQUI JOINs. Like the EQUI JOIN, the NATURAL JOIN also requires the equality of two column values as selection condition.

A NATURAL INNER JOIN on the tables “employees” and “de­part­ments” could be im­ple­men­ted as follows, for example:

The SQL statement instructs the DBMS to link the listed tables. The selection condition is created using the keyword USING, which specifies which columns are to be checked for equality. The pre­requis­ite is that a column “d_id” exists in both tables. Data records from both tables are only included in the result set if the DBMS finds identical values in the columns marked “d_id.”

The result table of the NATURAL JOIN differs from the classic INNER JOIN in that columns with the same names are not listed twice, but are merged into a common column.

Table: NATURAL JOIN between “employee” and “de­part­ments”

Instead of the “de­part­ment IDs” of both tables as “employees.d_id” and “de­part­ments.d_id,” only one column “d_id” is played.

NATURAL JOINs can be ab­bre­vi­ated without the USING clause. Instead, the NATURAL JOIN operator is used. The short notation of the above operation cor­res­ponds to the following SQL statement.

The NATURAL JOIN operator auto­mat­ic­ally connects tables using columns with the same names. The selection condition does not have to be defined ex­pli­citly.