FROM clause: Comma-separated syntax

Syntax

FROM { stream [ [AS] alias] | stream [ [AS] alias] keep_clause [keep_clause] | window_name [ [AS] alias] | ( nested_join [on_clause] ) |xmltable_exp |subquery } [, ...]

Usage

Use this variation of the FROM clause for:

• Single-source queries.

• Inner joins that use the comma-separated syntax. An inner join publishes all possible combinations of rows from the intersection of two or more specified data sources, according to the limitations of the selection condition (if present).

• Queries that use the MATCHING clause to detect patterns.

Any column or data source references in the statement's other clauses must be to one of the data sources named in this clause.

A nested join data source can include an ON subclause that establishes the nested join's selection condition. For more information about when the ON is required and when it is optional, see FROM Clause: Join Syntax.

Windows in the From Clause

Query statement data sources can include named or unnamed windows:

• Create named windows with a separate Create Window statement. These windows exist independently of the query and can simply be specified by name in the FROM clause, with or without an optional alias.

• Create unnamed windows within the FROM clause itself with one or two KEEP clauses, specifying one or two window policies. Window policies define how the window maintains state (retains rows). An unnamed window derives its schema from the stream to which it is linked.

Joins Specified with Comma-Separated Syntax

The comma-separated FROM clause can contain multiple data sources connected with an inner join. The multiple sources are separated by commas. An optional WHERE clause creates the selection condition for the join (the ON clause is not used to create a selection condition for comma-separated joins). In the absence of a WHERE clause, the join selects all values from all selected data source columns. This join syntax can include more than two data sources.

Pattern-Matching Syntax

Use the comma-separated FROM clause in conjunction with the MATCHING clause to specify the data sources that should be monitored for a specified pattern. When used in pattern detection, all data sources specified in the FROM clause must be data streams and must include all streams identified as events in the MATCHING clause. An optional ON clause creates the matching join condition for the query.

Aliases

Data sources specified in the FROM clause can include an alias, which can be used to refer to the data source anywhere in the query where the data source name would otherwise be used.

The use of an alias is required when the same column in the same data source is used more than once by the query's SELECT clause, or when a data stream is listed more than once in the MATCHING clause. In such cases, each use must also be listed as a separate data source in the FROM clause, and a unique alias assigned to each instance.

Restrictions

The following restrictions apply when you use the comma-separated FROM syntax to create a join:

• The list of data sources in the join can include only one data stream expression.

• No ON clause can be used to set a condition for the join, except in the case of nested joins using the JOIN word syntax.

The following restrictions apply when using the comma-separated FROM syntax in conjunction with the MATCHING clause:

• The list of data sources can include only data streams.

• If an ON clause is used, it must establish a relationship between all the data sources included in the FROM clause.

• The list of data sources must include all data sources specified in the MATCHING clause, and cannot include any other data sources.

See Also

• Database Statement

• Query Statement

• Remote Procedure Statement

• KEEP

• MATCHING

• ON

Example

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

INSERT INTO OutStream
SELECT S1.Symbol, S1.Average, S2.Average
FROM Stream1 AS S1 KEEP 10 ROWS, Stream2 AS S2 KEEP 5 MINUTES
WHERE S1.Symbol = S2.Symbol;