Jointed table (SAP Library - Reference Manual 7.2 (BC))

Jointed table

JOINED TABLE ( joined table) can be specified as part of a FROM TABLE specification (from table spec).

Syntax

<joined table> ::=
<from table spec> CROSS JOIN <from table spec>
| <from table spec> [INNER] JOIN <from table spec> <join spec>
| <from table spec> [<LEFT | RIGHT | FULL> [OUTER]] JOIN <from table spec> <join spec>

<join spec> ::= ON <search condition> | USING (<column name>,...)

from table spec, search condition, column name

Explanation

If a FROM TABLE specification comprises a JOINED TABLE, the result is generated as follows:

Let FT1 be the set of all rows in the table specified by the first from table specification. Let FT2 be the set of all rows in the table specified by the second from table specification.

If JOINED TABLE is specified as CROSS JOIN, a table is created that comprises all possible combinations of FT1 and FT2. From a mathematical perspective, the Cartesian product of the two tables is calculated.
If JOINED TABLE is specified with the keyword JOIN without the optional keywords INNER, LEFT, RIGHT, FULL, or OUTER, the JOIN type is assumed to be INNER.

Let T be the set of result rows consisting of all possible combinations of FT1 and FT2. Each result row satisfies the join specification for this set.

If JOINED TABLE is specified with the JOIN type INNER, the result is the set T.
If JOINED TABLE is specified with the JOIN type LEFT, the result is the set T plus the rows from FT1 that are not in T. The result columns that are not formed from FT1 are assigned the NULL value.
If JOINED TABLE is specified with the JOIN type RIGHT, the result is the set T plus the rows from FT2 that are not in T. The result columns that are not formed from FT2 are assigned the NULL value.
If JOINED TABLE is specified with the JOIN type FULL, the result is the set T plus the rows from FT1 that are added by the JOIN types LEFT and RIGHT.

The rules specified for the WHERE condition apply to the JOIN specification ( join spec ) ON <search condition> .

If the JOIN specification ( join spec ) USING (<column name>,...) is specified, the column names must denote columns that are contained in both FT1 and FT2 and for which the user has the SELECT privilege. Specifying the JOIN specification USING (<column name>,...) means the same as comparison predicates between the specified columns in FT1 and FT2 linked with AND. = is used as a comparison operator ( comp op).