Home > Documentation > SELECT Statement

SELECT Statement

Syntax

SELECT target_list
  FROM tuple_source [...]
  [WHERE predicate]
  [GROUP BY field_identifier_grouping[, ...]]
  [ORDER BY field_identifier_ordering[, ...] [LIMIT number]]

Substitutable Fields

target_list:

One or more entries, separated by commas, of the format target_list_entry.

target_list_entry:

A value, of the format expression [[AS] field_alias], to be included in the result set returned by the statement.

expression:

A value that will be included in the output tuple. Values may be obtained from a tuple field, from a table entry, and from a simple or aggregate function. Optionally, the name for a value may be modified through an alias.

field_alias:

An optional name (an alias) for a value in the result set. Alias names must be used for values derived from functions.

tuple_source:

A stream, windowed stream, or table from which the statement obtains values. Depending on how the SELECT statement is being used, acceptable entries are a single stream identifier, one or two windowed stream identifiers, or a stream identifier and a table identifier.

• Single unwindowed stream:

  	FROM {stream_identifier | subquery} [[AS] source_alias]
  	

• Single windowed stream:

  	FROM  {stream_identifier | subquery}
  	      '['window_specification  | window_identifier']'
  	      [[AS] source_alias]
      

• Table query:

  	FROM {stream_identifier | subquery}
           [[AS] source_alias]
           {, | OUTER JOIN }
           table_identifier [[AS] table_alias]
  	

• Tuple-based join:

  	FROM {stream_identifier_1 | subquery_1}
           '['window_specification_1 | window_identifier_1']'
           [[AS] source_alias_1], {stream_identifier_2 | subquery_2}
           '['window_specification_2 | window_identifier_2']'
           [[AS] source_alias_2]
  	

A single windowed stream does not require a comma between the stream identifier and the window specification. A simple join of a stream and table is indicated by separating their identifiers with a comma, while an outer join is indicated by separating the identifiers with the OUTER JOIN keywords. Use a comma to separate the two windowed streams used in a tuple-based join.

stream_identifier:

The unique identifier (name) of the stream.

subquery:

A parenthesized stream_expression.

stream_expression:

A StreamSQL statement that produces a stream.

source_alias:

A unique identifier or name (alias) used only within this statement for a tuple source providing input used by this statement.

window_identifier:

A named window specification previously declared with a CREATE WINDOW statement. Note that the square braces are a required part of the syntax.

window_specification:

A window specification defined within the FROM clause. Note that the square braces are a required part of the syntax.

predicate:

A clause that limits the result set returned by the SELECT statement. To be selected, a tuple, or row in a table, must satisfy the restriction, or restrictions, included in the predicate. If the predicate evaluates to true, the tuple (or row) is selected. If the WHERE clause is omitted from the SELECT statement, all tuples (or rows) are selected.

field_identifier_grouping:

An output field used to group the entries in the result set returned by the statement. When the SELECT statement is applied to a windowed stream, the GROUP BY clause must include each target_list_entry that is not derived from an aggregate function.

field_identifier_ordering:

An output field used to order the entries in the result set returned by the statement. If the SELECT statement is being used as a Table query (that is, to read from a table), the ordering column(s) must be indexed.

number:

The number of entries to include in the result set returned by the statement. The LIMIT keyword is only valid within the context of the ORDER BY clause.

Discussion

SELECT is used to retrieve tuples from a stream, one or two windowed stream, or a table. A SELECT statement includes required subclauses — such as FROM, which identifies the streams or table from which the tuples are extracted — and optional subclauses — for example WHERE, which restricts the number of tuples retrieved. A SELECT statement may also include nested SELECT statements, which are referred to as subqueries.

A target_list_entry represents a value that will be included in each row of the result set. An entry may be extracted from a tuple present on a stream, from a row in a table, or from the return from a function or expression. If a function or expression is used to generate a value, a field_alias must be specified. If the target list includes values derived from aggregate functions, the FROM clause must specify a windowed stream, and if the target list includes both tuple field values, simple function values, and/or scalar expressions and values derived from aggregate functions, each of the non-aggregate values must be listed in the GROUP BY clause.

If a target_list_entry is an aggregate function applied to a window definition, for example, the openval function, the syntax varies depending on whether the window was defined in a separate CREATE WINDOW statement or embedded within the stream identifier listed in the FROM clause. If a CREATE WINDOW statement is used, then the window identifier, within double quotation marks, is included as a parameter — openval("window_identifier"). If the window specification is included within the FROM clause, it does not have a window identifier. In this case, a parameter is not required to invoke these functions — openval().

When a SELECT statement is used to extract values from an unwindowed stream, the FROM clause may only include one tuple source entry. If the tuple source is a windowed stream, the target list includes aggregate function calls. When accessing a table, the FROM clause must include a stream tuple source and a table tuple source. Finally, when performing a tuple based join, the FROM clause must include two windowed streams.

With a table read, one or more field values from each tuple on the stream are used to select rows from the table. Consequently, each read may return a result set containing content from multiple table entries.

The ORDER BY field_identifier entries are the stream fields or table columns to use in ordering the result set. The optional LIMIT clause restricts the size of the result set. Note that the LIMIT clause must be used in conjunction with the ORDER BY clause.

The return from a SELECT statement must be captured into a stream. The CREATE STREAM statement (or possibly a CREATE OUTPUT STREAM statement) may be combined with an embedded SELECT statement or written as separate statements, as the following fragments illustrate.

CREATE STREAM stream_idenfifier AS
  SELECT ...;

Or

CREATE STREAM stream_identifier;
SELECT ... INTO stream_identifier;