Joins two data sources in a query using outer or inner join syntax.
FROM { source [(DYNAMIC|STATIC)] [AS] alias] [keep_clause] | nested_join }
[INNER|RIGHT|LEFT|FULL] JOIN
{ source [ [AS] alias] [keep_clause] | nested_join }
on_clause
| source | The name of a data stream, window, or delta stream |
| DYNAMIC | STATIC | DYNAMIC indicates the data in the window or stream being joined is going to change often and STATIC (the default) indicates that it won't |
| alias | An alias for the stream or window |
| keep_clause | An optional policy that specifies how rows are maintained in the window (it cannot be used with a stream or delta stream) |
| nested_join | A nested join — see below |
For outer joins, use an ON clause to specify the join condition. This is optional for inner joins.
| JOIN | If no join type is specified, the default is INNER. |
| INNER 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 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 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 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 queries. You can use aliases for 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.