SELECT statement

Syntax

simple [ { {UNION [ALL] | INTERSECT | EXCEPT } simple } [...] ] [ORDER BY column [ ASC[ENDING] | DESC[ENDING] ] [, ...]] [LIMIT {limit [OFFSET offset | {offset, limit} ]

Components

simple

select_clause from_clause [WHERE expression] [GROUP BY expression [, ...]] [HAVING expression]

Components

select_clause

SELECT [ ALL | DISTINCT ] { expression [[AS] alias] } [, ...]

Components

from_clause

FROM single | inner | outer

Components

single

{ pub_window [AS alias] } | { ( sub_select ) [AS alias] }

Components

inner

single [, ...] |single [INNER] JOIN single [ ON expression | USING ( ids ) ] |single NATURAL [INNER] JOIN single |single NATURAL [INNER] JOIN single

Components

outer

single { RIGHT |LEFT |FULL} OUTER JOIN single [ ON expression | USING ( ids ) ]

Components

Usage

The SQL Select statement queries the current contents of the public windows listed in its FROM clause and generates a result of zero or more rows of data, each of which has a fixed number of columns. In addition to any columns explicitly queried in the statement, the query result includes an initial TIMESTAMP column, containing the row timestamp.

The Select statement includes one or more simple-select components, each of which can contain the following clauses.

• The SELECT clause contains a select list of one or more items, each of which is an expression. When the expressions contain column names, the columns must be from one of the public windows listed in the query's FROM clause. The number of columns in the result of the Select statement is determined by the number of items contained in the select list of the SELECT clause.

• The FROM clause lists one or more public windows or subqueries against which the query is executed. Public windows are created with the CCL Create Window Statement, and are populated in the same manner as other CCL windows. The SQL Select statement is executed against the current output of the specified public windows or subqueries.

• The optional WHERE clause can be used to specify selection conditions and certain join conditions.

• The optional GROUP BY clause causes one or more rows of the result to be combined into a single row of output.

• The optional HAVING clause performs filtering after grouping has occurred.

A compound-select is formed from two or more simple-selects connected by one of the operators described in the following table. All simple-selects in a compound-select must specify the same number of result columns. The following operators can be used to create a compound-select:

When three or more simple-selects are connected into a compound-select, they group from left to right.

The Select statement can also contain no more than one of each of the following clauses:

• The optional ORDER BY clause causes output rows to be sorted.

• The optional LIMIT places an upper limit on the number of rows that can be returned in the result.

Select Clause

Every simple select of the Select statement must include one SELECT clause. This clause specifies a select-list, which is used to generate query results. The number of columns in the result is determined by the number of items in the query's select-lists. In compound selects, the connect operator also partially determines the number of columns in the query result.

A select-list consists of one or more comma-separated items, each of which is an arbitrary SQL-92 expression:

• A select-list expression can refer to column names of the public windows specified in the Select statement's FROM clause, as determined by the window schemas.

• If the FROM clause refers to multiple public windows, any ambiguous column reference in the select-list (usually a reference to a column name defined for more than one of the windows) must be specified in the format public-window-name.column-name.

• The asterisk (*) character can be used to select all columns from all public windows listed in the FROM clause. The asterisk can also be used in combination with a public window name, within a select-list item to indicate that all columns from the specific public window should be selected public-window-name.*.

• Rows from the public window listed in the FROM clause are passed to the SELECT clause after being filtered by the query's WHERE clause, if one is specified.

• The results of the expressions in the list are processed by other clauses (if any) in the query.

• Each select-list expression item can specify an AS output-alias subclause, indicating an alias by which the column results should be published. The item's alias can also be listed directly after the item, omitting the AS keyword.

The select-list can be prefaced by a DISTINCT keyword. If two or more rows contain the same values in all queried columns, the DISTINCT keyword causes only one of the rows to be included in the result. In the absence of the DISTINCT keyword, result rows are returned regardless of whether or not they are distinct. This default behavior can be specified explicitly by using the ALL keyword.

From Clause

The FROM clause specifies the data sources (public windows or subqueries) against which the Select statement is executed. Rows from these sources are filtered by the WHERE clause, if one is specified, and are then passed to the query's SELECT clause.

The FROM clause can refer to a single data source, or to multiple joined data sources. Each data source must be one of the following:

• A public window (created with the CCL Create Window statement).

• A subquery-nested Select statement, enclosed in parentheses.

When your SQL FROM clause refers to a public window that is located in the project's main module, refer to the window by the name you defined for it in the Create Window statement. When referring to a public window in one of the main module's submodules, use the following syntax in your name reference:

[ [ parent/ [...] ] submodule/window]

Data sources specified in the FROM clause can include an SQL alias, specified with the syntax data-source AS alias. This alias can be used to refer to the data source anywhere in the query where the data source name would otherwise be used. This clause can also be used without the AS keyword.

When the FROM clause refers to joined data sources, the joins can be specified using either the comma-separated format, or the JOIN keyword format. SQL supports standard SQL-92 syntax, which is summarized below.

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 one is present). Joined data sources can refer only to public windows in the same module or in the module's submodules. SQL provides four options for specifying an inner join:

SQL also supports the following standard syntax for outer joins. An ON or USING subclause is required with outer joins to establish the join condition.

Where Clause

The WHERE clause can be used to specify an expression, which is used for one of two purposes:

• As a selection condition, the WHERE clause expression filters rows from the query's data sources, before they are passed to the SELECT clause.

• As a join condition, the WHERE clause can be used with comma-separated inner joins to establish a relationship between the columns in the query's data sources, thus limiting the cross-product output of the join.

Expressions specified inside the WHERE clause cannot refer to aggregate functions.

Group By Clause

The expressions specified in the GROUP BY clause cause one or more rows of the result to be combined into a single row of output. GROUP BY is often used when the query result contains aggregate functions. GROUP BY clause expressions do not have to appear in the result, however.

Having Clause

The HAVING clause is similar to the WHERE clause, but is applied after grouping has occurred. The HAVING expression can refer to aggregate functions and to values that are not included in the result.

Order Clause

The ORDER BY clause sorts the query's output rows according to the specified list of expressions. These expressions do not have to be part of the result when used with a simple-select. When used with a compound-select, however, each sort expression must exactly match one of the result columns. Each expression can optionally be followed by either or both of the following:

• A COLLATE subclause, which specifies the name of a collating function used for ordering text.

• The ASC or DESC keywords, which are used to specify the sort order.

Only one ORDER BY clause can appear inside a SELECT statement.

Limit Clause

The LIMIT clause places an upper limit on the number of rows returned by the query. A negative LIMIT indicates no upper bound. Optionally, this clause can also indicate how many rows should be skipped at the beginning of the result set. Three syntax forms are permitted for the LIMIT clause:

When used in conjunction with a compound select the LIMIT clause can be used only once, but is applied to all the simple selects in the query.