SQL - Joins (Home)

> (Data|State) Management and Processing > (Data Type|Data Structure) > (Relation|Table) - Tabular data > Structured Query Language (SQL)

1 - About

A join is a SQL clause statement which define the set operation (intersection, union, …) that the database must perform between two or more set of data stored generally in tables to retrieve a data set (the result set).

The result set is beautifully illustrated with a Venn_diagram.

The tables usually have a parent-child relationship.

If a query does not contain a join, the database returns a result set that contains all possible combinations of the rows in the query tables. Such a result set is known as a Cartesian product.

You use joins to ensure that queries returning data from multiple tables do not return incorrect results. A join between two tables defines how data is returned.

A join is characterized by multiple tables in the FROM clause, and the relationship between the tables is defined through the existence of a join condition in the WHERE clause.

Cardinalities further describe a join between 2 tables by stating how many rows in one table will match rows in another.

In a join :

  • one row set is called inner
  • and the other is called outer.

Disambiguation: The term join can be use to define:


3 - Type of Joins

4 - SQL Join Problems

In the design of a semantic layer (such as a SAP BOBJ - Universe for BOBJ or the repository for OBIEE), you may need to resolve join problems such as :

  • loops,
  • chasm traps,
  • and fan traps,

which may occur in the structure by using aliases and/or contexts.


5 - Documentation / Reference