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 table-expression ]
[ WHERE search-condition ]
[ GROUP BY [ group-by-expression ]
[ HAVING search-condition ]
[ WINDOW window-name AS window-spec
[ , window-name AS window-spec ... ] ]
[ ORDER BY { expression | integer } [ ASC | DESC ], ... ]
[ FOR { UPDATE [ cursor-concurrency ] | READ ONLY } ]
[ FOR XML xml-mode ]
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 :
{ column-name | expression } [ [ AS ] alias-name ], ...
| *| window-function OVER { window-name | window-spec }
group-by-expression:
See .
cursor-concurrency :
BY { VALUES | TIMESTAMP | LOCK }
xml-mode :
RAW [ , ELEMENTS ] | AUTO [ , ELEMENTS ] | EXPLICIT
window-function :RANK ( )
| DENSE_RANK ( )
| PERCENT_RANK ( )
| CUME_DIST ( )
| ROW_NUMBER ( )
| aggregate-function
window-spec:
( [window-name ]
[ PARTITION BYcolumn-reference [ collate-clause ]
[ , column-reference [ collate-clause ] ... ] ]
[ ORDER BY order-by-expression, ... ]
[ { ROWS | RANGE } { window-frame-start | window-frame-between } ]
)
window-frame-start :
{ UNBOUNDED | integer-expression } PRECEDING
| CURRENT ROW
window-frame-between :
BETWEEN window-frame-bound-1
AND window-frame-bound-2
window-frame-bound:
window-frame-start
| { UNBOUNDED | integer-expression } FOLLOWING
WITH or WITH RECURSIVE 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.
For more information, see .
ALL or DISTINCT 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 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 .
select list The select list is a list of expressions, separated by commas, specifying what will be 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 ). Subqueries are also allowed in the select list (see ). 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 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 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 This clause is used to create a table and fill it with data.
If the table name starts # then 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 the following conditions:
The select-list contains more than one item, and the INTO target is a single table-name identifier, or
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 statement 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,
SELECT @@version
displays the value of the global variable @@version. This is equivalent to:
SELECT @@version FROM DUMMY
For more information, see .
WHERE clause This clause specifies which rows will be 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).
For more information, see and .
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. All NULL-containing rows are treated as a single set. The resulting rows are often referred to as groups since there is one row in the result for each group of rows from the table list. 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.
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 will be 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, Adaptive Server 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 This clause specifies whether updates are allowed through a cursor opened on the query. Note that this clause cannot be used with the FOR XML clause.
If you do not use a FOR clause in the SELECT statement, the updatability is specified by the API. For ODBC and OLE DB, the default is read only. For JDBC, Open Client, and embedded SQL, the default is update.
This clause overrides the embedded SQL DECLARE CURSOR statement. However, it may be overridden by the concurrency setting in other programming interfaces. In ODBC and OLE DB, the read-only default setting overrides the FOR clause, but if you change the default to something other than read only, the FOR clause is not overridden. In JDBC and Open Client, the current setting always overrides the FOR clause, whether or not it is the default (updatable cursors).
Statement updatability is dependent on the setting of the ANSI_UPDATE_CONSTRAINTS database option. Other characteristics of the statement are also considered, including whether the statement contains a DISTINCT, GROUP BY, HAVING, UNION, aggregate functions, joins, or non-updatable views.
For more information about cursor sensitivity, see .
For more information about ODBC concurrency, see the discussion of SQLSetStmtAttr in .
For more information about the ANSI_UPDATE_CONSTRAINTS database option, see .
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. Note that 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.
For information about writing a query using EXPLICIT mode, see .
For more information about using the FOR XML clause, see .
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. The 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.
SQL/92 Entry-level feature. The complexity of the SELECT statement means that you should check individual clauses against the standard.
SQL/99 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.
Sybase Supported by Adaptive Server Enterprise, with some differences in syntax.
How many employees are there?
SELECT count(*) FROM employee
List all customers and the total value of their orders.
SELECT company_name, CAST( sum(sales_order_items.quantity * product.unit_price) AS INTEGER) VALUE FROM customer JOIN sales_order JOIN sales_order_items JOIN product GROUP BY company_name ORDER BY VALUE DESC
The following statement shows an Embedded SQL SELECT statement:
SELECT count(*) INTO :size FROM employee