Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SELECT statement

Use this statement to retrieve information from the database.

[ WITH temporary-views ]
SELECT [ ALL | DISTINCT ] [ row-limitation ] select-list
[ INTO { hostvar-list | variable-list | 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 { UPDATE [ cursor-concurrency ] | READ ONLY } ]
[ FOR XML xml-mode ]
[ 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 :
FIRST | TOP n [ START AT m ]

select-list :
expression [ [ AS ] alias-name ], ...
| *
| window-function OVER { window-name | window-spec }
[ [ AS ] alias-name ]

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

cursor-concurrency :
BY { VALUES | TIMESTAMP | LOCK }

xml-mode :
RAW [ , ELEMENTS ] | AUTO [ , ELEMENTS ] | EXPLICIT

query-hint :
MATERIALIZED VIEW OPTIMIZATION option-value
| FORCE OPTIMIZATION
| option-name = option-value

option-name : identifier

option-value : hostvar (indicator allowed), string, identifier, or number

WITH 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 expressions, only if the RECURSIVE keyword is specified. Mutually recursive common table expressions are not supported.

This clause is permitted only if the SELECT statement appears in one of the following locations:

• Within a top-level SELECT statement

• Within the top-level SELECT statement of a VIEW definition

• Within a top-level SELECT statement within an INSERT 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.

See Common Table Expressions.

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 clause    Explicitly limit the rows of queries that include ORDER BY clauses. The TOP value must be an integer constant or integer variable with value greater than or equal to 0. The START AT value must be an integer constant or integer variable with a value greater than 0.

For more information about the use of FIRST and TOP, see Explicitly limiting the number of rows returned by a query.

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 (see SQL Functions). Subqueries are also allowed in the select-list (see Expressions). 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 will display the expression itself.

INTO hostvar-list clause    This clause is used in embedded SQL only. It specifies where the results of the SELECT statement will 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.

INTO variable-list clause    This clause is used in procedures and triggers only. It specifies where the results of the SELECT statement will 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.

If the table name starts #, it is created as a temporary table. Otherwise, the table is created as a permanent base table. 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.

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;

See FROM clause.

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). See Search conditions and 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 will 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 may 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. See WINDOW clause.

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 will be used. This clause cannot be used with the FOR XML clause.

When you specify FOR UPDATE BY TIMESTAMP or FOR UPDATE BY VALUES, the database server uses optimistic concurrency by using a keyset-driven cursor. In this situation, lost updates can occur.

If you do not use a FOR clause in the SELECT statement, the updatability of a cursor depends on the cursor's declaration (see DECLARE statement and FOR statement) and how cursor concurrency is specified by the API. In ODBC, JDBC, and OLE DB, statement updatability is explicit and a read-only, forward-only cursor is used unless it is overridden by the application. In Open Client, embedded SQL, and within stored procedures, cursor updatability does not have to be specified, and the default is FOR UPDATE.

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

In addition to cursor updatability, statement updatability is also 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 more information about cursor sensitivity, see SQL Anywhere cursors.

For more information about ODBC concurrency, see the discussion of SQLSetStmtAttr in Choosing ODBC cursor characteristics.

For more information about the ansi_update_constraints database option, see ansi_update_constraints option [compatibility].

For more information about cursor updatability, see Understanding updatable statements.

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.

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. See Using FOR XML EXPLICIT.

For more information about using the FOR XML clause, see Using the FOR XML clause to retrieve query results as XML.

OPTION clause    This clause provides hints as to how to process the query. The following query hints are supported:

• MATERIALIZED VIEW OPTIMIZATION 'option-value'  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. See materialized_view_optimization option [database].

• FORCE OPTIMIZATION  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. In some cases 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-base 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.
  For more information about simple queries and view matching, see Phases of query processing, and Improving performance with materialized views.

• option-name = option-value  Specify an option setting that takes precedence over any public or temporary option settings that are in effect, for this statement only. The supported options are:

  • isolation_level option [compatibility]
  • max_query_tasks option [database]
  • optimization_goal option [database]
  • optimization_level option [database]
  • optimization_workload option [database]

The SELECT statement is used for retrieving results from the database.

A SELECT statement can be used in Interactive SQL to browse data in the database, or to export data from the database to an external file.

A SELECT statement can also be used 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.

A SELECT statement can also be used to return a result set from a procedure.

Must have SELECT permission on the named tables and views.

None.

• Expressions
• FROM clause
• Search conditions
• UNION statement
• Joins: Retrieving Data from Several Tables

• SQL/2003    Core feature. The complexity of the SELECT statement means that you should check individual clauses against the standard. For example, the ROLLUP keyword is part of feature T431.

  FOR UPDATE, FOR READ ONLY, and FOR UPDATE ( column-list ) are core features.

  FOR UPDATE BY [ LOCK | TIMESTAMP | VALUES ] is a SQL Anywhere vendor extension.

This example returns the total number of employees in the Employees table.

SELECT COUNT(*)
FROM Employees;

This example 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:

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' );