Contents Index SAVEPOINT statement SET statement

ASA SQL Reference
  SQL Statements

SELECT statement


Description 

Use this statement to retrieve information from the database.

Syntax 

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

Parameters 

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:

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:

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.

Usage 

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.

Permissions 

Must have SELECT permission on the named tables and views.

Side effects 

None.

See also 

Expressions

FROM clause

Search conditions

UNION operation

Joins: Retrieving Data from Several Tables

Standards and compatibility 
Example 

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

Contents Index SAVEPOINT statement SET statement