FROM Clause: ANSI Syntax

Syntax

FROM { source [ [(DYNAMIC|STATIC)] [AS] alias] [keep_clause] | nested_join }
[INNER|RIGHT|LEFT|FULL] JOIN
{ source [ [(DYNAMIC|STATIC)] [AS] alias] [keep_clause] | nested_join }
on_clause

Components

source	The name of a data stream, window, reference, or delta stream.
DYNAMIC \| STATIC	DYNAMIC indicates the data in the window or stream being joined is going to change often. A secondary index is created on windows joining with an incomplete primary key of a DYNAMIC window or stream. This improves performance but uses additional memory proportional to the total data length of key columns in the index. By default, windows and streams are STATIC and no secondary indices are created.
alias	An alternate name for the source.
keep_clause	An optional policy that specifies how rows are maintained in the window (it cannot be used with a reference, stream, or delta stream).
nested_join	A nested join — see below.

Usage

For outer joins, use an ON clause to specify the join condition. This is optional for inner joins.

You can use this variation of FROM to create inner, left , right, and full joins:

JOIN	If no join type is specified, the default is INNER.
INNER JOIN	All possible combinations of rows from the intersection of both datasources (limited by the selection condition, if one is specified) are published.
RIGHT JOIN	All possible combinations of rows from the intersection of both datasources (limited by the selection condition, if one is specified) are published. All the other rows from the right datasource are also published. Unmatched columns in the left datasource publish a value of NULL .
LEFT JOIN	All possible combinations of rows from the intersection of both datasources (limited by the selection condition, if one is specified) are published. All the other rows from the left datasource are also published. Unmatched columns in the right datasource publish a value of NULL.
FULL JOIN	All possible combinations of rows from the intersection of both datasources (limited by the selection condition, if one is specified) are published. All other rows from both datasources are published as well. Unmatched columns in either datasource publish a value of NULL .

The datasources used with this syntax can include data stream expressions, named and unnamed window expressions, queries, and references. You can use aliases for the datasources in this variation of the FROM clause.

The join variation of the FROM clause (ANSI syntax) is limited to two datasources. Accommodate additional datasources using a nested join as one of the datasources. If a nested join is used, it can optionally be enclosed in parentheses, and can include its own ON clause. The rules for the use of the ON clause 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

Other clauses in the query can only refer to one of the datasources named in this clause.
A reference can't be on the outer side of the join.
For a left outer join, the data stream must be on the left [or outer] side.
For a right outer join, the data stream must be on the right [or outer] side.
A full outer join cannot join a window to a data stream.
A full outer join cannot include a reference.
Only one of the datasources in a join can be a reference.
Joining a window to a reference without a primary key can only produce a stream or a window with aggregation.
When producing a window from the join, if the reference has a primary key, that entire key must be bound in the WHERE clause. And the resultant window cannot contain any columns from the reference in its primary key.
Only one streaming element can be joined to reference without a primary key in a single query.
When a join is a stream, and a reference with a primary key is used in the join, but the entire primary key is not bound (as required to produce a unique key) it is treated like a reference that does not have a primary key defined.