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.
SELECTstatements are built from a
select-expression, see The SELECT Expression, optionally followed by a
FOR UPDATE OFclause.
SELECTstatements 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.
The FOR UPDATE Clause
SELECTstatement defines a cursor intended for
UPDATE CURRENTstatements, the
for-updateclause must specified. If the
FOR UPDATE OFversion is used, it must include all the columns to be updated.
Each column specified in the
for-update-ofmust belong to the table or view named in the
fromclause of the
SELECTstatement, although the columns in FOR UPDATE OF do not need to be specified in the
selectclause. No column may be named more than once in the
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 OF may not be specified if the statement defines a read-only result set, see Updatable Result Sets.
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
- there are no set-functions in the
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
EXCEPTis not included
- the keyword
INTERSECTis 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
FOR UPDATEclause has been specified.
A cursor which addresses a read-only result table may not be used for
The FOR READ ONLY Clause
FOR READ ONLYclause is optional since
SELECTstatements by default are read-only.
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 OFFSET 10 ROWS FETCH 5 ROWS ONLY;List all artists and use the
FETCH FIRSTconstruction to pick one arbitrary album for each artist.SELECT a.artist, (SELECT p.product FROM mimer_store.products AS p JOIN mimer_store.items AS i ON p.product_id = i.product_id JOIN mimer_store_music.titles AS t ON i.item_id = t.item_id WHERE t.artist_id = a.artist_id FETCH 1) AS work_sample FROM mimer_store_music.artists AS a;Find the 10 most common starts of artist names, leading `The' excluded:SELECT strt, count(*) AS cnt FROM (SELECT CASE WHEN artist NOT LIKE 'The %' THEN CAST(artist AS nchar(3)) ELSE SUBSTRING(artist FROM 5 FOR 3) END AS strt FROM mimer_store_music.artists) GROUP BY strt ORDER BY cnt DESC FETCH FIRST 10;
SQL-2016 Core Fully compliant. SQL-2016 Features outside core Feature F302, "INTERSECT table operator".Feature F304, "EXCEPT ALL table operator".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 F857, "Top-level <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 F861, "Top-level <result offset clause> in <query expression>".Feature F862, "<result offset clause> in subqueries".Feature F865, "dynamic <offset row count> in <result offset clause>".Feature T551, "Optional keywords for default syntax" support for the keyword DISTINCT. Mimer SQL Extension Support for host variable in <fetch first clause> and <result offset clause> is a Mimer SQL extension.
Mimer Information Technology AB
Phone: +46 18 780 92 00