SELECT

---

Retrieves data from the tables in the database.

where query-expression is:

Usage

Embedded/Interactive/ODBC/Procedural/JDBC.

In ESQL, the SELECT statement may only be used to declare a cursor or as input to a PREPARE statement.

In a Procedural usage context, the SELECT statement may only be used to declare a cursor.

In interactive SQL, the SELECT statement is used for interactive data retrieval. See the Mimer SQL User's Manual, Retrieving Data for more details.

Description

The ORDER BY Clause

The final result table may be ordered according to an ORDER BY clause. 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. 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 OF sequence.

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

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

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.

The FOR UPDATE OF Clause

If the SELECT statement defines a cursor for UPDATE CURRENT statements, a FOR UPDATE OF clause may be optionally used to list the columns to be updated. If the FOR UPDATE OF clause is used, it must include all the columns to be updated.

Each column specified in the FOR UPDATE CLAUSE must belong to the table or view named in the FROM clause of the SELECT statement, although the columns in FOR UPDATE OF do not need to be specified in the SELECT clause. No column may be named more than once in the FOR UPDATE OF clause.

Column names in the FOR UPDATE OF clause may not be qualified by the name of the table or view. They are implicitly qualified by the table reference in the FROM clause of the select specification.

FOR UPDATE OF may not be specified if the statement defines a read-only result set, see Updatable Result Sets.

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.

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

SELECT Statements

Simple SELECT statements are built from a select-specification, see The SELECT Specification, optionally followed by either an ORDER BY or a FOR UPDATE OF clause. More complex statements can combine two or more select-specifications with the UNION operator.

Select statements are used in embedded SQL (including procedural usage contexts) to define cursors and as the input to dynamic PREPARE statements.

The embedded SELECT statement is syntactically equivalent to the interactive data retrieval SELECT statement. 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 DISTINCT is not specified
• all the result columns are specified as column-names and no column-name appears more than once
• the FROM clause 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 INNER or OUTER JOIN
• the GROUP BY clause is not included
• the HAVING clause is not included
• the keyword UNION is not included
• the ORDER BY clause 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 DELETE CURRENT or UPDATE CURRENT statements.

Examples

 SELECT 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;

Standard Compliance

Standard	Compliance	Comments
SQL-2003	Core	Fully compliant.
SQL-2003	Features outside core	Feature T551, "Optional keywords for default syntax" support for the keyword DISTINCT.

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