ASA SQL Reference
SQL Statements
Use this statement to retrieve information from the database.
[ WITH temporary-views ]
SELECT [ ALL | DISTINCT ] [ row-limitation ] select-list
[ INTO { hostvar-list | variable-list } ]
[ FROM table-expression ]
[ WHERE search-condition ]
[ GROUP BY [ group-by-list | ROLLUP ] (group-by-list) ]
[ HAVING search-condition ]
[ 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 ], ...
| *
group-by-list :
{ column-name | alias-name | function | expression }, ...
cursor-concurrency :
BY { VALUES | TIMESTAMP | LOCK }
xml-mode :
RAW [ , ELEMENTS ] | AUTO [ , ELEMENTS ] | EXPLICIT
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.
For more information, see Common Table Expressions.
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.
FIRST or TOP Explicitly limit the rows of queries that include ORDER BY clauses. For queries using the TOP n option, a START AT value of m restricts the result set to at most n rows starting at row m. The default value of m is 1.
For more information about the use of FIRST and TOP, see Explicitly limiting the number of rows returned by a query.
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 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 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.
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 FROM clause.
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 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. 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.
ROLLUP keyword The ROLLUP operation adds summary rows into the result set of a query with a GROUP BY clause.
A prefix is a subset of the items in the group-by-list. A prefix is defined by excluding one or more rightmost items from those in the group-by-list. An additional row is added to the result set for each prefix. The additional row contains subtotal information for a set of rows in the GROUP BY result set. Each subtotal row includes the following:
Column excluded from the prefix NULL
Column included in the prefix The value of the column
Aggregate function An aggregate over the values of the excluded columns.
For more information about ROLLUP operations, see The ROLLUP operation: adding summary information to GROUP BY queries.
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 Adaptive Server Anywhere cursors.
For more information about ODBC concurrency, see the discussion of SQLSetStmtAttr in Choosing a cursor characteristics.
For more information about the ANSI_UPDATE_CONSTRAINTS database option, see ANSI_UPDATE_CONSTRAINTS option [compatibility].
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 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.
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.
Joins: Retrieving Data from Several Tables
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