SELECT Specification Syntax

---

The select-specification syntax is:

Each clause in the select-specification construction is 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.

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:

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 two 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.

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.

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

The COLLATE clause determines the order of the resulting 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.

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).

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.

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-2003	Core	Sub-feature E051-09 "Rename columns in the FROM clause" not supported.

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