SELECT Specification Syntax
Each clause in the
select-specificationconstruction is described in detail in the following sections.
The SELECT Clause
SELECTclause 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.
This form of the
SELECTclause specifies all columns in the Cartesian product of the tables specified in the
FROMclause. The single asterisk may not be combined with any other value specification.
ExampleSELECT * 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.
If a named table or view (
correlation-name) is followed by an asterisk in the
SELECTclause, all columns are selected from that table or view.
This formulation may be used in a list of select specifications.
correlation-nameis used, it must be defined in the associated
FROMclause, 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.
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
A column name must be qualified if more than one column in the set of table references addressed in the
FROMclause has the same name.
SELECT ... AS Column-label
column-labelmay be added after each separate expression in the
column-labelis 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.
SELECT COLUMN_NAMEwould result in a column called
COLUMN_NAMEin the result set, but
SELECT COLUMN_NAME + 1would result in a column in the result set with no name.
The Keywords ALL and DISTINCT
ALLis specified or if no keyword is given, duplicate rows are not eliminated from the result of the
DISTINCTis specified, duplicate rows are eliminated.
NULLis considered to be equal to
NULLin this context.
The FROM Clause and Table-reference
FROMclause defines an intermediate result set for the select-specification, and may define correlation names for the table references used in the result set.
All source tables and views referenced in the
SELECTclause and at the top level in the
WHEREclause (but not in any subselect used in the
WHEREclause) must be named in the
Intermediate Result Sets
If a single table or view is named in the
FROMclause, the intermediate result set is identical to the table or view.
FROMclause 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 introduced in the
FROMclause 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
FROMclause if it does not have a correlation name. The same table or view name cannot be exposed more than once in the same
The same correlation name may not be introduced more than once in the same
FROMclause, and it cannot be the same as an exposed table or view name.
The WHERE Clause
WHEREclause selects a subset of the rows in the intermediate result set on the basis of values in the columns. If no
WHEREclause is specified, all rows of the intermediate result set are selected.
All column references in the
search-conditionmust uniquely identify a column in the intermediate result set defined by the
FROMclause 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
GROUP BYclause determines grouping of the result table for the application of set functions specified in the
GROUP BYclause has the following syntax:
GROUP BYclause is specified, each column reference in the
SELECTlist 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.
GROUP BYclause is not specified, the
SELECTlist 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
COLLATEclause determines the order of the resulting data.
COLLATEclause 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
HAVINGclause restricts selection of groups in the same way that a
WHEREclause restricts selection of rows.
HAVINGclause has the following syntax:
The search condition in the
HAVINGclause defines restrictions on the values in the elements of the
SELECTlist. Column references in the search condition of the
HAVINGclause must identify a grouping column, or be used in set functions, or be outer references.
HAVINGis used together with
GROUP BY, in which case the search conditions relate either values in grouping columns or results of set functions to expressions.
HAVINGclause is used without a
GROUP BYclause, all rows in the result table are treated as a single group. In this case, the
HAVINGclause must refer to a set function (since there are no grouping columns).
HAVINGclause may not be specified in a
FROMclause names a view that uses
HAVING, or in a
select-specificationused in a basic predicate.
SELECTaccess is required on all tables and views specified in a
SELECTstatement is used without the
ORDER BYclause, 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.
This section summarizes standard compliance for
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