The SELECT Expression

---

A select-expression defines a set of data (rows and columns) extracted from one or more tables or views.

The select-expression syntax is:

where select-expression-body is:

where the select-specification syntax is:

and order-by-clause is:

and result-offset-clause is:

and fetch-first-clause is:

The different clauses in the specifications above are described in detail in the following sections.

The SELECT Clause

The SELECT clause defines which values are to be selected. Values are specified by column references or expressions; where columns are addressed, the value selected is the content of the column.

SELECT *

This form of the SELECT clause specifies all columns in the Cartesian product of the tables specified in the FROM clause. The single asterisk may not be combined with any other value specification.

Example

 SELECT * FROM countries ...
 

Note: Use of SELECT * is discouraged in programs (except in EXISTS predicates) since the asterisk is expanded to a column list when the statement is compiled, and any subsequent alterations in the table or view definitions may cause the program to function incorrectly.

SELECT table.*

If a named table or view (table-name or correlation-name) is followed by an asterisk in the SELECT clause, all columns are selected from that table or view.

This formulation may be used in a list of select specifications.

If a correlation-name is used, it must be defined in the associated FROM clause, see The FROM Clause and Table-reference.

Note: Use of SELECT table.* is discouraged in programs (except in EXISTS predicates) since the asterisk is expanded to a column list when the statement is compiled, and any subsequent alterations in the table or view definitions may cause the program to function incorrectly.

SELECT expression

Values to be selected may be specified as expressions (using column-references, set functions and literals, see Expressions).

Column names used in expressions must refer to columns in the tables addressed in the FROM clause or be an outer reference.

A column name must be qualified if more than one column in the set of table references addressed in the FROM clause has the same name.

SELECT ... AS Column-label

A column-label may be added after each separate expression in the SELECT clause. column-label is an SQL identifier which becomes the name of the column in the result set.

If no name is given the original column name is used, unless the new column was created by an expression, in which case the new column has no name.

For example, SELECT COLUMN_NAME would result in a column called COLUMN_NAME in the result set, but SELECT COLUMN_NAME + 1 would result in a column in the result set with no name.

The Keywords ALL and DISTINCT

If ALL is specified or if no keyword is given, duplicate rows are not eliminated from the result of the select-specification.

If DISTINCT is specified, duplicate rows are eliminated. NULL is considered to be equal to NULL in this context.

The FROM Clause and Table-reference

The FROM clause defines an intermediate result set for the select-specification, and may define correlation names for the table references used in the result set.

where table-reference is:

where correlation is:

Mimer SQL Engine
Mimer SQL Engine does not support the( select-expression ) part of the table-reference syntax. (I.e. select in from list is not supported.)

General Syntax

All source tables and views referenced in the SELECT clause and at the top level in the WHERE clause (but not in any subselect used in the WHERE clause) must be named in the FROM clause.

Intermediate Result Sets

If a single table or view is named in the FROM clause, the intermediate result set is identical to the table or view.

If the FROM clause names more than one table or view, the intermediate result set may be regarded as the complete Cartesian product of the named tables or views.

Note: The intermediate result set is a conceptual entity, introduced to aid in understanding of the selection process. The complete result set does not have any direct physical existence, so that the machine resources available do not need to correspond to the (sometimes very large) Cartesian product tables implied by multiple table references in a FROM clause.

Correlation Names

Correlation names introduced in the FROM clause redefine the form of the table name which may be used to qualify column names, see Qualified Object Names.

Correlation names may be used for several purposes:

• to shorten table names, which saves typing and makes statements easier to follow and less error-prone.
• to relate a table to a logical copy of itself.
• to rename a column when a column with the same name exists in another of the query's tables.

A table or view name is exposed in the FROM clause if it does not have a correlation name. The same table or view name cannot be exposed more than once in the same FROM clause.

The same correlation name may not be introduced more than once in the same FROM clause, and it cannot be the same as an exposed table or view name.

Mimer SQL Engine
Column rename is not supported.

The WHERE Clause

The WHERE clause selects a subset of the rows in the intermediate result set on the basis of values in the columns. If no WHERE clause is specified, all rows of the intermediate result set are selected.

All column references in the search-condition must uniquely identify a column in the intermediate result set defined by the FROM clause or be an outer reference.

Column references must be qualified if more than one column in the intermediate result set has the same name, or if the column is an outer reference.

The GROUP BY Clause

The GROUP BY clause determines grouping of the result table for the application of set functions specified in the SELECT clause.

The GROUP BY clause has the following syntax:

If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function.

The rows of the intermediate result set are (conceptually) arranged in groups, where all values in the grouping column(s) are identical within each group.

Each group is reduced to a single row in the final result of the select-specification.

If a GROUP BY clause is not specified, the SELECT list must either be a list that does not include any set functions or a list of set functions and optional literal expressions.

The COLLATE Clause

Collations determine the sort order of character data. If the COLLATE clause is specified, the resulting data will be grouped according to the collation specified. For more information, see the Mimer SQL User's Manual, Collations.

If no COLLATE clause is specified, the column's implicit collation will be used.

The HAVING Clause

The HAVING clause restricts selection of groups in the same way that a WHERE clause restricts selection of rows.

The HAVING clause has the following syntax:

The search condition in the HAVING clause defines restrictions on the values in the elements of the SELECT list. Column references in the search condition of the HAVING clause must identify a grouping column, or be used in set functions, or be outer references.

Most commonly, HAVING is used together with GROUP BY, in which case the search conditions relate either values in grouping columns or results of set functions to expressions.

If the HAVING clause is used without a GROUP BY clause, all rows in the result table are treated as a single group. In this case, the HAVING clause must refer to a set function (since there are no grouping columns).

Mimer SQL Engine
The GROUP BY or HAVING clause may not be specified in a select-specification whose FROM clause names a view that uses GROUP BY or HAVING, or in a select-specification used in a basic predicate.

The UNION Operator

If several SELECT statements are connected by UNION or UNION DISTINCT, the result is derived by first merging all result tables specified by the separate SELECT statements, and then eliminating duplicate rows from the merged set. All columns in the result table are significant for the purpose of eliminating duplicates.

The UNION ALL operator on the other hand retains all duplicates. The operator can be viewed as a way to concatenate several queries.

The rules described below apply to both UNION and UNION ALL.

All separate result tables from SELECT statements connected by UNION must have the same number of columns and the data types of columns to be merged must be compatible.

The columns in the result table are named in accordance with the columns in the first SELECT statement of the UNION construction.

Separate SELECT statements may be enclosed in parentheses if desired. This does not affect the result of a UNION operation.

The names in the first select specification are used in UNION constructions.

See Result Data Types for a description of how the data type of the UNION result is determined.

The INTERSECT Operator

If several SELECT statements are connected by INTERSECT or INTERSECT DISTINCT, the result is derived by taking the results of two queries and return only rows that appear in both result sets, and then eliminating duplicate rows from the merged set. All columns in the result table are significant for the purpose of eliminating duplicates.

The INTERSECT ALL operator on the other hand retains all duplicates.

The rules described below apply to both INTERSECT and INTERSECT ALL.

All separate result tables from SELECT statements connected by INTERSECT must have the same number of columns and the data types of columns to be merged must be compatible.

The columns in the result table are named in accordance with the columns in the first SELECT statement of the INTERSECT construction.

Separate SELECT statements may be enclosed in parentheses if desired. This does not affect the result of a INTERSECT operation.

The names in the first select specification are used in INTERSECT constructions.

See Result Data Types for a description of how the data type of the INTERSECT result is determined.

Mimer SQL Engine
The INTERSECT operator is not supported.

The EXCEPT Operator

If several SELECT statements are connected by EXCEPT or EXCEPT DISTINCT, the result is derived by taking the distinct rows of the first query and return the rows that do not appear in second result query. All columns in the result table are significant for the purpose of eliminating duplicates.

The EXCEPT ALL operator on the other hand retains all duplicates.

The rules described below apply to both EXCEPT and EXCEPT ALL.

All separate result tables from SELECT statements connected by EXCEPT must have the same number of columns and the data types of columns to be merged must be compatible.

The columns in the result table are named in accordance with the columns in the first SELECT statement of the EXCEPT construction.

Separate SELECT statements may be enclosed in parentheses if desired. This does not affect the result of a EXCEPT operation.

The names in the first select specification are used in EXCEPT constructions.

See Result Data Types for a description of how the data type of the EXCEPT result is determined.

Mimer SQL Engine
The EXCEPT operator is not supported.

The ORDER BY Clause

The result table may be ordered according to an order-by-clause.

The ORDER BY clause has the following syntax:

Every expression in the order-by-clause must contain a reference to a column in a table specified in the FROM clause.

Column labels, created with SELECT AS, may not be part of a complex ORDER BY expression, (i.e. if column label is used, the expression must contain nothing but the column label).

The ORDER BY expressions must not include set functions (i.e. MAX, MIN, AVG, SUM and COUNT), subqueries or NEXT VALUE FOR sequence.

If DISTINCT, GROUP BY or UNION, EXCEPT or INTERSECT is specified, only columns from the result set may be specified as ORDER BY expressions.

The default collation for sorting data is the collation defined for the column being sorted. If you include a COLLATE clause, you can override the default collation by explicitly specifying a different collation. For more information, see the Mimer SQL User's Manual, Collations.

Ascending/Descending

For each column in the order-by-clause, the sort order may be specified as ASC (ascending) - the default, or DESC (descending). If more than one column is specified, the result table is ordered first by values in the first specified column, then by values in the second, and so on.

Mimer SQL Engine
Only top-level order-by-clause is supported. (I.e. ORDER BY is not supported in subqueries.)

The RESULT OFFSET Clause

The result-offset-clause is used to limit the result set by removing a specified number of rows from its beginning.

The result-offset-clause clause has the following syntax:

If a statement contains both an order-by-clause and a result-offset-clause, the result set is first sorted according to the ORDER BY clause, and then the number of rows specified in the result-offset-clause are removed.

Mimer SQL Engine
Only top-level result-offset-clause is supported. (I.e. OFFSET is not supported in subqueries.) Only an integer value or a host-variable can be used to specify the OFFSET value.

The FETCH FIRST Clause

The fetch-first-clause is used to limit the result set by specifying the number of rows to be returned.

The fetch-first-clause has the following syntax:

If a statement contains both an order-by-clause and a fetch-first-clause, the result set is first sorted according to the order-by-clause and then limited to the number of rows specified in the fetch-first-clause.

If both a result-offset-clause and a fetch-first-clause are specified, the result-offset-clause is applied first, then the fetch-first-clause.

Mimer SQL Engine
Only top-level fetch-first-clause is supported. (I.e. FETCH FIRST is not supported in subqueries.) Only an integer value or a host-variable can be used to specify the FETCH FIRST value.

Restrictions

SELECT access is required on all tables and views specified in a FROM clause.

Notes

If the SELECT statement is used without the ORDER BY clause, the sort order is undefined. This means that the sort order may change if new indexes are created, indexes are dropped, new statistics are gathered or if a new version of the SQL optimizer is installed.

Standard Compliance

This section summarizes standard compliance for select-specifications.

Standard	Compliance	Comments
SQL-2011	Core	Mimer SQL Engine: Sub-feature E051-09, "Rename columns in the FROM clause" is not supported. Sub-feature E071-03, "EXCEPT DISTINCT" is not supported. Mimer SQL Experience: Fully compliant.
SQL-2011	Features outside core	Feature T551, "Optional keywords for default syntax" support for the keyword DISTINCT. Feature F857, "Top-level <fetch first clause> in <query expression>" Feature F861, "Top-level <result offset clause> in <query expression>". Feature T211, "Basic trigger capability". Feature T551, "Optional key words for default syntax". Mimer SQL Experience: Feature F302, "INTERSECT table operator". Feature F304, "EXCEPT ALL table operator". Feature F591, "Derived tables". Feature F851, "<order by clause> in subqueries". Feature F855, "Nested <order by clause> in <query expression>". Feature F856," Nested <fetch first clause> in <query expression>". Feature F858, "<fetch first clause> in subqueries". Feature F860, "dynamic <fetch first row count> in <fetch first clause>". Feature F862, "<result offset clause> in subqueries". Feature F863, "Nested <result offset clause> in <query expression>". Feature F865, "dynamic <offset row count> in <result offset clause>".
	Mimer SQL Extension	Support for host variable in <fetch first clause> and <result offset clause> is a Mimer SQL extension.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se