FROM clause: Join syntax

Syntax

FROM { stream [ [AS] alias] | stream [ [AS] alias] keep_clause [keep_clause] | window_name [ [AS] alias] |nested_join|xmltable_exp |subquery } [ RIGHT | LEFT | FULL ] [OUTER] JOIN { stream [ [AS] alias] | stream [ [AS] alias] keep_clause [keep_clause] | window_name [ [AS] alias] |nested_join |xmltable_exp |subquery }

Components
stream	The name of a data stream.
alias	An alias for the stream or window.
keep_clause	The policy that specifies how rows are maintained in the window. See KEEP Clause for more information.
window_name	The name of a window.
nested_join	A nested join. See nested_join for more information.
xmltable_exp	Produces an XML table. See XMLTABLE Expressions in the FROM Clause for more information.
subquery	A subquery. See CCL Subqueries in the FROM Clause for more information.

nested_join

FROM { stream [ [AS] alias] | stream [ [AS] alias] keep_clause [keep_clause] |window_name [ [AS] alias] |nested_join |xmltable_exp |subquery} [ RIGHT | LEFT | FULL ] [OUTER] JOIN { stream [ [AS] alias] | stream [ [AS] alias] keep_clause [keep_clause] | window_name [ [AS] alias] |nested_join |xmltable_exp |subquery } [on_clause]

Components
stream	The name of a data stream.
alias	An alias for the stream or window.
keep_clause	The policy that specifies how rows are maintained in the window. See KEEP Clause for more information.
window_name	The name of a window.
nested_join	Another nested join.
xmltable_exp	Produces an XML table. See XMLTABLE Expressions in the FROM Clause for more information.
subquery	A subquery. See CCL Subqueries in the FROM Clause for more information.
on_clause	The join condition. See ON Clause: Join Syntax for more information.

Usage

This variation of the FROM clause is used for creating inner and outer joins that use the JOIN keyword syntax. Any column or data source references in the query's other clauses must be to one of the data sources named in this clause.

For outer joins, the use of an ON clause defining the join selection condition is required. The ON clause is optional for inner joins using the FROM data source JOIN data source syntax. Other clauses, including the WHERE clause, can be used for further data filtering.

This variation of FROM can be used to create inner, left outer, right outer, and full outer joins as follows:

JOIN	All possible combinations of rows from the intersection of both data sources (limited by the selection condition, if one is specified) are published.
RIGHT OUTER JOIN	All possible combinations of rows from the intersection of both data sources (limited by the selection condition, if one is specified) are published. All the other rows from the right data source are also published. Unmatched columns in the left data source publish a value of `NULL`.
LEFT OUTER JOIN	All possible combinations of rows from the intersection of both data sources (limited by the selection condition, if one is specified) are published. All the other rows from the left data source are also published. Unmatched columns in the right data source publish a value of `NULL`.
FULL OUTER JOIN	All possible combinations of rows from the intersection of both data sources (limited by the selection condition, if one is specified) are published. All other rows from both data sources are published as well. Unmatched columns in either data source publish a value of `NULL`.

The data sources used with this syntax can include data stream expressions, named and unnamed window expressions, and subqueries. Data stream and window expressions can also be combined with XMLTABLE expressions. XMLTABLE expressions and CCL subqueries are discussed in XMLTABLE Expressions in the FROM Clause and CCL Subqueries in the FROM Clause. For more information about named and unnamed windows in the FROM clause, see Windows in the From Clause.

Data sources in this variation of the FROM clause can be aliased. For more information on aliasing, see Aliases.

The JOIN variation of the FROM clause is limited to two data sources. Additional data sources can be accommodated by using a nested join as one of the data sources, or by using CCL subqueries, described in CCL Subqueries in the FROM Clause. If a nested join is used, it must be enclosed in parentheses, and can include its own ON subclause. The rules for the use of the ON subclause with a nested join are the same as the rules that govern the use of the ON clause in the join containing the nested join.

Restrictions

The list of data sources cannot contain more than one data stream expression.
A full outer join cannot join a window to a data stream.
A left outer join that joins a window to a data stream must list the data stream on the left; a right outer join that joins a window to a data stream must list the data stream on the right.

Example

The FROM clause in the following example creates a two-window full outer join.