Retrieves information from the database.
Syntax[ WITH temporary-views ] SELECT [ ALL | DISTINCT ] [ row-limitation-option-1 ] select-list [ INTO { hostvar-list | variable-list | table-name } ] [ INTO LOCAL TEMPORARY TABLE { table-name } ] [ FROM from-expression ] [ WHERE search-condition ] [ GROUP BY group-by-expression ] [ HAVING search-condition ] [ WINDOW window-expression ] [ ORDER BY { expression | integer } [ ASC | DESC ], ... ] [ FOR READ ONLY | for-update-clause ] [ FOR XML xml-mode ] [ row-limitation-option-2 ] [ OPTION( query-hint, ... ) ]
temporary-views :
regular-view, ...
| RECURSIVE { regular-view | recursive-view }, ...regular-view : view-name [ ( column-name, ... ) ] AS ( subquery )
recursive-view : view-name ( column-name, ... ) AS ( initial-subquery UNION ALL recursive-subquery )
row-limitation-option-1 : FIRST | TOP { ALL | limit-expression } [ START AT startat-expression ]
row-limitation-option-2 : LIMIT { [ offset-expression, ] limit-expression | limit-expression OFFSET offset-expression }
limit-expression : simple-expression
startat-expression : simple-expression
offset-expression : simple-expression
simple-expression :
integer
| variable
| ( simple-expression )
| ( simple-expression { + | - | * } simple-expression )select-list : expression [ [ AS ] alias-name ], ... | * | window-function OVER { window-name | window-spec } [ [ AS ] alias-name ] | sequence-expression
sequence-expression sequence-name [ CURRVAL | NEXTVAL ] FROM table-name
sequence-expression : See Expressions.
from-expression : See FROM clause.
group-by-expression : See GROUP BY clause.
search-condition : See Search conditions.
window-name : identifier
window-expression : See WINDOW clause.
window-spec : See WINDOW clause.
window-function : RANK( ) | DENSE_RANK( ) | PERCENT_RANK( ) | CUME_DIST( ) | ROW_NUMBER( ) | aggregate-function
for-update-clause FOR UPDATE | FOR UPDATE cursor-concurrency | FOR UPDATE OF [ ( column-name, ... ) ]
cursor-concurrency : BY { VALUES | TIMESTAMP | LOCK }
xml-mode : RAW [ , ELEMENTS ] | AUTO [ , ELEMENTS ] | EXPLICIT
query-hint : MATERIALIZED VIEW OPTIMIZATION option-value | FORCE OPTIMIZATION | FORCE NO OPTIMIZATION | option-name=option-value
option-name : identifier
option-value : hostvar (indicator allowed) | string | identifier | number
ParametersWITH or WITH RECURSIVE clause Define one or more common table expressions, also known as temporary views, to be used elsewhere in the remainder of the statement. These expressions may be non-recursive, or may be self-recursive. Recursive common table expressions may appear alone, or intermixed with non-recursive table expressions, only if the RECURSIVE keyword is specified. Mutually recursive common table expressions are not supported.
This clause is permitted only if the SELECT query block appears in one of the following locations:
Within top-level SELECT query block including the top-level SELECT query block of a view definition
Within a top-level SELECT statement within an INSERT query block
Within a nested SELECT query block defining a derived table in any type of SQL statement
Recursive expressions consist of an initial subquery and a recursive subquery. The initial-query implicitly defines the schema of the view. The recursive subquery must contain a reference to the view within the FROM clause. During each iteration, this reference refers only to the rows added to the view in the previous iteration. The reference must not appear on the null-supplying side of an outer join. A recursive common table expression must not use aggregate functions and must not contain a GROUP BY, ORDER BY, or DISTINCT clause.
The WITH clause is not supported with remote tables. The WITH clause may also be used in INTERSECT, UNION, and EXCEPT query blocks.
ALL or DISTINCT clause ALL (the default) returns all rows that satisfy the clauses of the SELECT statement. If DISTINCT is specified, duplicate output rows are eliminated. Many statements take significantly longer to execute when DISTINCT is specified, so you should reserve DISTINCT for cases where it is necessary.
row-limitation clauses The row limitation clauses allow you to return only a subset of the rows that satisfy the WHERE clause. Only one row-limitation clause can be specified at a time. When specifying these clauses, an ORDER BY clause is required to order the rows in a meaningful manner.
row-limitation-option-1 The TOP and START AT arguments can be simple arithmetic expressions over host variables, integer constants, or integer variables. The TOP argument must evaluate to a value greater than or equal to 0. The START AT argument must evaluate to a value greater than 0.
If startat-expression is not specified, the default is 1. If the argument of TOP is ALL, all rows starting at startat-expression are returned. The TOP limit-expression START AT startat-expression clause is equivalent to LIMIT ( startat-expression -1 ), limit-expression or LIMIT limit-expression OFFSET ( startat-expression -1 ).
row-limitation-option-2 The LIMIT and OFFSET arguments can be simple arithmetic expressions over host variables, integer constants, or integer variables. The LIMIT argument must evaluate to a value greater than or equal to 0. The OFFSET argument must evaluate to a value greater than or equal to 0. If offset-expression is not specified, the default is 0.
The row limitation clause LIMIT offset-expression, limit-expression is equivalent to LIMIT limit-expression OFFSET offset-expression. Both of these constructs are equivalent to TOP limit-expression START AT ( offset-expression + 1 ).
The LIMIT keyword is disabled by default. Use the reserved_keywords option to enable the LIMIT keyword.
select-list clause The select-list is a list of expressions, separated by commas, specifying what is retrieved from the database. An asterisk (*) means select all columns of all tables in the FROM clause.
Aggregate functions are allowed in the select-list. Subqueries are also allowed in the select-list. Each subquery must be within parentheses.
Alias names can be used throughout the query to represent the aliased expression.
Alias names are also displayed by Interactive SQL at the top of each column of output from the SELECT statement. If the optional alias name is not specified after an expression, Interactive SQL displays the expression.
INTO clause Following are the three uses of the INTO clause:
INTO hostvar-list clause This clause is used in embedded SQL only. It specifies where the results of the SELECT statement go. There must be one host variable item for each item in the select-list. select-list items are put into the host variables in order. An indicator host variable is also allowed with each host variable, so the program can tell if the select-list item was NULL.
If the query results in no rows being selected, the variables are not updated, and a row not found warning appears.
INTO variable-list clause This clause is used in procedures and triggers only. It specifies where the results of the SELECT statement go. There must be one variable for each item in the select-list. select-list items are put into the variables in order.
INTO table-name clause This clause is used to create a table and fill it with data.
For permanent tables to be created, the query must satisfy one of the following conditions:
The select-list contains more than one item, and the INTO target is a single table-name identifier.
The select-list contains a * and the INTO target is specified as owner.table.
To create a permanent table with one column, the table name must be specified as owner.table.
This statement causes a COMMIT before execution as a side effect of creating the table. RESOURCE authority is required to execute this statement. No permissions are granted on the new table: the statement is a short form for CREATE TABLE followed by INSERT...SELECT.
Tables created using this clause do not have a primary key defined. You can add a primary key using ALTER TABLE. A primary key should be added before applying any UPDATEs or DELETEs to the table; otherwise, these operations result in all column values being logged in the transaction log for the affected rows.
INTO LOCAL TEMPORARY TABLE This clause is used to create a local, temporary table and populate it with the results of the query. When you use this clause, you do not need to start the temporary table name with #.
FROM clause Rows are retrieved from the tables and views specified in the table-expression. A SELECT statement with no FROM clause can be used to display the values of expressions not derived from tables. For example, these two statements are equivalent and display the value of the global variable @@version.
SELECT @@version; SELECT @@version FROM DUMMY; |
WHERE clause This clause specifies which rows are selected from the tables named in the FROM clause. It can be used to do joins between multiple tables, as an alternative to the ON phrase (which is part of the FROM clause).
GROUP BY clause You can group by columns, alias names, or functions. The result of the query contains one row for each distinct set of values in the named columns, aliases, or functions. As with DISTINCT and the set operations UNION, INTERSECT, and EXCEPT, the GROUP BY clause treats NULL values in the same manner as any other value in each domain. In other words, multiple NULL values in a grouping attribute form a single group. Aggregate functions can then be applied to these groups to get meaningful results.
When GROUP BY is used, the select-list, HAVING clause, and ORDER BY clause must not reference any identifier that is not named in the GROUP BY clause. The exception is that the select-list and HAVING clause can contain aggregate functions.
HAVING clause This clause selects rows based on the group values and not on the individual row values. The HAVING clause can only be used if either the statement has a GROUP BY clause or the select-list consists solely of aggregate functions. Any column names referenced in the HAVING clause must either be in the GROUP BY clause or be used as a parameter to an aggregate function in the HAVING clause.
WINDOW clause This clause defines all or part of a window for use with window functions such as AVG and RANK.
ORDER BY clause This clause sorts the results of a query. Each item in the ORDER BY list can be labeled as ASC for ascending order (the default) or DESC for descending order. If the expression is an integer n, then the query results are sorted by the nth item in the select-list.
The only way to ensure that rows are returned in a particular order is to use ORDER BY. In the absence of an ORDER BY clause, SQL Anywhere returns rows in whatever order is most efficient. This means that the appearance of result sets may vary depending on when you last accessed the row and other factors.
In embedded SQL, the SELECT statement is used for retrieving results from the database and placing the values into host variables via the INTO clause. The SELECT statement must return only one row. For multiple row queries, you must use cursors.
FOR UPDATE or FOR READ ONLY clause These clauses specify whether updates are allowed through a cursor opened on the query, and if so, what concurrency semantics can be used. This clause cannot be used with the FOR XML clause.
If you do not use a FOR clause in the SELECT statement, the updatability of a cursor depends on the cursor's declaration and how cursor concurrency is specified by the API. In ODBC, JDBC, OLE DB, ADO.NET, and embedded SQL, statement updatability is explicit and a read-only cursor is used unless it is overridden by the application. In Open Client and within stored procedures, cursor updatability does not have to be specified, and the default is FOR UPDATE.
For Open Client and stored procedures, cursor updatability and statement updatability is dependent on the setting of the ansi_update_constraints database option and the specific characteristics of the statement, including whether the statement contains ORDER BY, DISTINCT, GROUP BY, HAVING, UNION, aggregate functions, joins, or non-updatable views. For stored procedures, cursors default to FOR UPDATE for single-table queries without an ORDER BY clause, or if the ansi_update_constraints option is set to Off. When the ansi_update_constraints option is set to Cursors or Strict, then cursors over a query containing an ORDER BY clause default to READ ONLY. However, you can explicitly mark cursors as updatable using the FOR UPDATE clause. Because it is expensive to allow updates over cursors with an ORDER BY clause or a join, cursors over a query containing a join of two or more tables are READ ONLY and cannot be made updatable unless the ansi_update_constraints database option is Off.
A cursor declared FOR READ ONLY cannot be used in UPDATE (positioned), DELETE (positioned), or PUT statements. FOR READ ONLY is the default for embedded SQL.
The FOR UPDATE clause explicitly makes a cursor updatable. The use of FOR UPDATE alone does not, by itself, affect concurrency control on the rows in the result set of the statement. To do this, you must specify either FOR UPDATE BY LOCK or FOR UPDATE BY [ VALUES | TIMESTAMP ].
FOR UPDATE BY LOCK clause The database server acquires intent row locks on fetched rows of the result set. These are long-term locks that are held until the transaction is committed or rolled back.
FOR UPDATE BY { VALUES | TIMESTAMP } When you specify FOR UPDATE BY TIMESTAMP or FOR UPDATE BY VALUES, the database server uses optimistic concurrency by using a keyset-driven (value-sensitive) cursor. In this situation, lost updates can occur if the application modifies a row outside of the cursor (using a separate statement) or if the application does not heed the warnings and/or errors generated by the server indicating that the row has been modified by another connection.
To ensure that a statement acquires an intent lock, you must do one of the following:
specify FOR UPDATE BY LOCK in the query
specify HOLDLOCK, WITH ( HOLDLOCK ), WITH ( UPDLOCK ), or WITH ( XLOCK ) in the FROM clause of the query
open the cursor with API calls that specify CONCUR_LOCK
fetch the rows with attributes indicating fetch for update
The FOR UPDATE OF clause explicitly names the columns that can be modified with an UPDATE (positioned), DELETE (positioned), or PUT statement. You cannot use this clause in combination with any other FOR UPDATE, FOR READ ONLY, or FOR XML clause.
FOR UPDATE OF column-list clause When you specify the FOR UPDATE OF clause, the database server restricts the columns that can be modified with a positioned UPDATE or positioned DELETE statement to those columns that are explicitly named in that clause. An attempt to modify another column results in an error indicating that the column cannot be found. No check is made to determine if a column referenced within the list actually exists, or if that column's table is an updatable table.
FOR XML clause This clause specifies that the result set is to be returned as an XML document. The format of the XML depends on the mode you specify. This clause cannot be used with the FOR UPDATE or FOR READ ONLY clause. Cursors declared with FOR XML are implicitly READ ONLY.
When you specify RAW mode, each row in the result set is represented as an XML <row> element, and each column is represented as an attribute of the <row> element.
AUTO mode returns the query results as nested XML elements. Each table referenced in the select-list is represented as an element in the XML. The order of nesting for the elements is based on the order that tables are referenced in the select-list.
EXPLICIT mode allows you to control the form of the generated XML document. Using EXPLICIT mode offers more flexibility in naming elements and specifying the nesting structure than either RAW or AUTO mode.
OPTION clause This clause provides hints about how to process the query. The following query hints are supported:
MATERIALIZED VIEW OPTIMIZATION clause Use the MATERIALIZED VIEW OPTIMIZATION clause to specify how the optimizer should make use of materialized views when processing the query. The specified option-value overrides the materialized_view_optimization database option for this query only. Possible values for option-value are the same values available for the materialized_view_optimization database option.
FORCE OPTIMIZATION clause When a query specification contains only simple queries (single-block, single-table queries that contain equality conditions in the WHERE clause that uniquely identify a specific row), it typically bypasses cost-based optimization during processing. Sometimes you may want cost-based optimization to occur. For example, if you want materialized views to be considered during query processing, view matching must occur. However, view matching only occurs during cost-based optimization. If you want cost-based optimization to occur for a query, but your query specification contains only simple queries, specify the FORCE OPTIMIZATION option to ensure that the optimizer performs cost-based optimization on the query.
Similarly, specifying the FORCE OPTIMIZATION option in a SELECT statement inside of a procedure forces the use of the optimizer for any call to the procedure. In this case, plans for the statement are not cached.
FORCE NO OPTIMIZATION clause Specify the FORCE NO OPTIMIZATION clause if you want the statement to bypass the optimizer. If the statement is too complex to process in this way—possibly due to the setting of database options or characteristics of the schema or query—the statement fails and the database server returns an error.
option-name = option-value Specify an option setting. The setting you specify is only applicable to the current statement and takes precedence over any public or temporary option settings, including those set by ODBC-enabled applications. The supported options are:
If you specify the isolation_level option in a query, the value specified in the query takes precedence over all other isolation level settings (such as setting the isolation_level option for the database or the setting for the cursor) for the current query.
sequence-expression You can select the current value (CURRVAL) or next value (NEXTVAL) from a sequence generator.
RemarksThe SELECT statement can be used:
for retrieving results from the database.
in Interactive SQL to browse data in the database, or to export data from the database to an external file.
in procedures and triggers or in embedded SQL. A SELECT statement with an INTO clause is used for retrieving results from the database when the SELECT statement only returns one row. For multiple row queries, you must use cursors.
to return a result set from a procedure.
PermissionsMust have SELECT permission on the named tables and views.
To select the CURRVAL or NEXTVAL values from a sequence generator, you must have DBA authority, be the owner of the sequence, or have been granted permission to use the sequence generator.
Side effectsNone.
See also Standards and compatibilitySQL/2008 Core feature. The complexity of the SELECT statement means that you should check individual clauses against the standard. For example, the ROLLUP keyword, which can be specified in a GROUP BY clause, is part of optional SQL/2008 language feature T431. Some of the SQL/2008 optional language features supported by SQL Anywhere include:
The WINDOW clause and WINDOW aggregate functions comprise optional SQL/2008 language features T611 and T612.
Sequence expressions are part of feature T176.
Common table expressions are optional SQL/2008 language feature T121. A common table expression included in a nested query expression is feature T122. WITH RECURSIVE is optional SQL/2008 language feature T131; if included in a nested query it constitutes feature T132.
The ability to specify an ORDER BY clause with a query expression involving UNION, EXCEPT, or INTERSECT is optional feature F850. The ability to specify ORDER BY in a subquery is feature F851.
In the SQL standard, FOR UPDATE and FOR READ ONLY are part of a cursor declaration.
SQL Anywhere offers support for many extensions to the SQL/2008 definition of the SELECT statement. Some of these include:
The optional cursor-concurrency clause (FOR UPDATE BY { LOCK | VALUES | TIMESTAMP}) is a vendor extension.
The FOR XML, OPTION, and INTO clauses are vendor extensions.
The row limitation clause is a vendor extension. In the SQL/2008 standard, row limitation is supported using FETCH FIRST syntax, which is optional language feature F856. The syntax for feature F856 is not supported by SQL Anywhere.
The ability to specify ORDER BY n is a vendor extension.
In SQL/2008, all cursors except INSENSITIVE cursors are updatable by default. The read-only default with embedded SQL programs is a vendor extension.
Transact-SQL There are substantial differences in SELECT statement support between SQL Anywhere and Adaptive Server Enterprise. Several features of the SELECT statement are not supported by Adaptive Server Enterprise.
These differences include:
Sybase ASE does not support SQL Anywhere's cursor concurrency clause; to acquire a lock on a fetched row, you must use the HOLDLOCK table hint.
Adaptive Server Enterprise does not support recursive queries or common table expressions.
There are differences between Adaptive Server Enterprise and SQL Anywhere with respect to Transact-SQL outer joins.
In Transact-SQL you use the SELECT statement to assign a value to a variable, rather than with the Watcom SQL SET statement.
ExampleThis query returns the total number of employees in the Employees table.
SELECT COUNT(*) FROM Employees; |
This query lists all customers and the total value of their orders.
SELECT CompanyName, CAST( SUM( SalesOrderItems.Quantity * Products.UnitPrice ) AS INTEGER ) VALUE FROM Customers JOIN SalesOrders JOIN SalesOrderItems JOIN Products GROUP BY CompanyName ORDER BY VALUE DESC; |
The following statement shows an embedded SQL SELECT statement where the number of employees in the Employees table is selected into the :size host variable:
SELECT COUNT(*) INTO :size FROM Employees; |
The following statement is optimized to return the first row in the result set quickly:
SELECT Name FROM Products GROUP BY Name HAVING COUNT( * ) > 1 AND MAX( UnitPrice ) > 10 OPTION( optimization_goal = 'first-row' ); |
 |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |