StreamBase Documentation


SELECT Statement

Current Location: Home > StreamSQL Guide > SELECT Statement

Syntax

SELECT target_list
  FROM tuple_source [...]
  [WHERE predicate]
  [HAVING predicate]
  [GROUP BY field_identifier_grouping[, ...]]
  [ORDER BY field_identifier_ordering[, ...] [DESC] [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, materialized window, 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]
  • Materialized window: 
    	FROM {stream_identifier | subquery} [[AS] source_alias]  OUTER JOIN  materialized_window_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 of a stream and a table or a stream and a materialized window 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.

table_identifier:

A named table previously declared with a CREATE TABLE statement.

materialized_window_identifier:

A named materialized window previously declared with a CREATE MATERIALIZED WINDOW statement.

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. In a WHERE clause, if the predicate evaluates to true, the tuple (or row) is selected. In a HAVING clause, the predicate sets limits on which rows will be returned.

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 an unwindowed stream, one or two windowed streams, a materialized window or a table. A SELECT statement includes required subclauses — such as FROM, which identifies the streams, materialized window 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 tuple in a materialized window, from a row in a table, or from the return from a StreamBase 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, materialized window, or a table. If the target list of a SELECT statement run against a windowed stream includes tuple field values, simple function values or scalar expressions, and values derived from aggregate functions, each of the non-aggregate values must be listed in the GROUP BY clause.

When 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 listed with the stream identifier in the FROM clause. If a CREATE WINDOW statement is used, then the window identifier 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 includes only one tuple source entry: the unwindowed stream tuple source. If the tuple source is a windowed stream, the FROM clause includes a stream and window specification. When accessing a materialized window, the FROM clause must include a stream tuple source and a materialized window tuple source, and 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 materialized window or table read, one or more field values from each tuple on the incoming stream are used to select rows from the window or table. Consequently, the SELECT statement may return a result set containing content from multiple window or table entries.

If the WHERE clause is omitted from the SELECT statement, all tuples or rows are selected from the stream, materialized window, or table.

The HAVING clause is only valid when a SELECT statement is run against a windowed stream.

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;

Back to Top ^

Related Topics

Back to Top ^