join (SQL)

Joins are used in SQL to connect tables and/or views. There are joins with filter character, so-called inner joins, and those without filter character, the outer joins. Each join is done by so-called keys.

In relational databases, data is normalized to avoid redundancies and data errors. For example, addresses and the corresponding locations are stored in separate tables. To use an address with a location, the address table and the location table must be linked. In SQL this is achieved with a link. Since the SQL-92 standard, the join clause has been provided as a syntax component:

Join clause as syntax component (above) and older syntax (below).

The join is always done by means of one or more key values. In the above example, the key is the Place_ID column. If a Place_ID in the Address table matches a Place_ID in the Place table, the two records from the two tables are merged into a single record. Simplified, the following relations can occur:

1. The 1:n relation: For each Place_ID in the Place table there can be 0, 1 or more Place_IDs in the Address table. The record of the Location table is thereby linked to each corresponding record of the Address table.
2. The 1:1/0 link: For each Place_ID in the Place table there can be 0 or max. 1 corresponding record in the Address table. This case can occur, for example, in swap tables whose purpose is to reduce the amount of data in the main table by swapping rarely used data into a separate table.
3. The m:n link: Here the same place_ID can occur several times in the table Place as well as in the table Address. Since the database cannot assign the link unambiguously, it generates all possible link variants. This multiplies the number of resulting records of the link. For a place_ID: If the same place_ID exists 3x in place and 10x in address resulting 30 data records.
   

   Graphical representation of the three join types

   In the worst case, a so-called Cartesian square results, i.e. an "all with all" join. M:n relations occur frequently in domain-oriented definitions, but should be avoided on the data level in most cases or replaced by a more suitable structure. This can be, e.g., an intermediate link table, which maps an m:n relation, but contains a 1:n link to each of the source tables.

There are three types of mates:

1. Links with filter character, the inner joins: They form the intersection between two tables, in which only the records are contained which have a key contained in both tables.
2. Links without filter character, the outer joins: Here every record of the first table is contained in any case, regardless of whether there is a corresponding key in the second table. Where this key is missing, a dummy record is created from empty values(null values) as a replacement for the columns of the second table.
3. An extension of the outer joins are the full outer joins. With these all data records of both linked tables occur in any case. Where there is a join, it is made, where there is none, a dummy record is created as in [2.] Note: Full outer joins always create partial m:n relations.

SQL queries and statements often contain multiple joins in practice. This allows complex data structures over several tables to be combined in one query or statement, e.g. address, city, country, person, contract, etc.