Retrieves data from the tables in the database.
In ESQL, the
SELECTstatement may only be used to declare a cursor or as input to a
In a Procedural usage context, the
SELECTstatement may only be used to declare a cursor.
In interactive SQL, the
SELECTstatement is used for interactive data retrieval. See the Mimer SQL User's Manual, Retrieving Data for more details.
The ORDER BY Clause
The final result table may be ordered according to an
ORDER BYclause. Every expression in the
ORDER BYclause must contain a reference to a column in a table specified in the
Column labels, created with
SELECT AS, may not be part of a complex
ORDER BYexpression, (i.e. the expression must contain nothing but the column label).
ORDER BYexpressions must not include set functions (i.e.
COUNT), subqueries or
UNION ALLis specified, only columns from the result set may be specified as
The names in the first select specification are used in
The default collation for sorting data is the collation defined for the column being sorted. If you include a
COLLATEclause, you can override the default collation by explicitly specifying a different collation. For more information, see the Mimer SQL User's Manual, Collations.
For each column in the
ORDER BYclause, 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.
The FOR UPDATE OF Clause
SELECTstatement defines a cursor for
UPDATE CURRENTstatements, a
FOR UPDATE OFclause may be optionally used to list the columns to be updated. If the
FOR UPDATE OFclause is used, it must include all the columns to be updated.
Each column specified in the
FOR UPDATE CLAUSEmust belong to the table or view named in the
FROMclause of the
SELECTstatement, although the columns in
FOR UPDATE OFdo not need to be specified in the
SELECTclause. No column may be named more than once in the
FOR UPDATE OFclause.
Column names in the
FOR UPDATE OFclause may not be qualified by the name of the table or view. They are implicitly qualified by the table reference in the
FROMclause of the select specification.
FOR UPDATE OFmay not be specified if the statement defines a read-only result set, see Updatable Result Sets.
The UNION Operator
SELECTstatements are connected by
UNION DISTINCT, the result is derived by first merging all result tables specified by the separate
SELECTstatements, and then eliminating duplicate rows from the merged set. All columns in the result table are significant for the purpose of eliminating duplicates.
UNION ALLoperator 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
All separate result tables from
SELECTstatements connected by
UNIONmust 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
SELECTstatement of the
SELECTstatements may be enclosed in parentheses if desired. This does not affect the result of a
See Result Data Types for a description of how the data type of the
UNIONresult is determined.
SELECTstatements are built from a
select-specification, see The SELECT Specification, optionally followed by either an
ORDER BYor a
FOR UPDATE OFclause. More complex statements can combine two or more select-specifications with the
Select statements are used in embedded SQL (including procedural usage contexts) to define cursors and as the input to dynamic
SELECTstatement is syntactically equivalent to the interactive data retrieval
SELECTstatement. In embedded contexts however, the statement cannot be used to retrieve data directly but must be implemented through a cursor.
Updatable Result Sets
A result set is only updatable if all of the following conditions are true (otherwise the result set is read-only):
- the keyword
DISTINCTis not specified
- all the result columns are specified as column-names and no column-name appears more than once
FROMclause specifies exactly one table reference and that table reference refers either to a base table or an updatable view
- the result set is not the product of an explicit
GROUP BYclause is not included
HAVINGclause is not included
- the keyword
UNIONis not included
ORDER BYclause is not included
- it is not the result of a call to a result set procedure.
A cursor which addresses a read-only result table may not be used for
ExamplesSELECT format, category_id FROM formats ORDER BY LOWER(format), category_id; SELECT format AS format_name, category FROM formats ORDER BY CASE category WHEN 'ROCK' THEN 1 WHEN 'JAZZ' THEN 2 ELSE 3 END;
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40