SELECT statement

• 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 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 a 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.

  This functionality is available only in the Watcom SQL dialect.

  WITH RECURSIVE
    manager ( EmployeeID, ManagerID,
              GivenName, Surname, mgmt_level ) AS
  ( ( SELECT EmployeeID, ManagerID,       -- initial subquery
             GivenName, Surname, 0
      FROM Employees AS e
      WHERE ManagerID = EmployeeID )
    UNION ALL
    ( SELECT e.EmployeeID, e.ManagerID,   -- recursive subquery
             e.GivenName, e.Surname, m.mgmt_level + 1
      FROM Employees AS e JOIN manager AS m
       ON   e.ManagerID =  m.EmployeeID
        AND e.ManagerID <> e.EmployeeID
        AND m.mgmt_level < 20 ) )
  SELECT 'Manager', * FROM manager
  WHERE mgmt_level > 0
  UNION ALL
  SELECT 'Employee', * FROM manager
  WHERE mgmt_level = 0
  ORDER BY mgmt_level, Surname, GivenName;
  

• 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 reserve use of 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.

  Note

  The following characters are not permitted in aliases:

  • Double quotes

  • Control characters (any character less than 0X20)

  • Backslashes

  • Square brackets

  • Back quotes

• 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 tables to be created, the query must satisfy one of the following conditions:

    • The table-name is a temporary table name or is specified as owner.table-name.

    • The select-list contains a * ore more than one item.

    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. No privileges 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 clause

  This clause is used to create a local, temporary table and populate it with the results of the query. When you use this clause, it is not necessary to start the temporary table name with #.

• INTO TABLE table-name clause

  This clause always creates a table and populates it with the results of the query. The INTO TABLE clause behaves the same way as the INTO table-name clause, with the exception that the query does not need to satisfy the select-list conditions of the INTO table-name clause.

• INTO VARIABLE variable-list clause

  This clause specifies where the results of the SELECT statement go. The values returned by the query are assigned to the fields of the row variable in order.

  If there is a single item in the INTO clause and multiple items in the select-list, then the single item is interpreted as a row variable and the returned column values are assigned to the fields of the row variable in order. If the INTO clause contains more than one item, then the INTO variable-list semantics are applied.

  Note

  The row variable cannot include another composite data type, such as a row or array, and it must be composed as a collection of simple variables; otherwise, an error is returned.

• 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 SYS.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, the database server returns rows in whatever order is most efficient. The appearance of result sets may vary depending on when you last accessed the row and other factors.

  Items in the ORDER BY clause cannot be table reference variables (variables defined as type TABLE REF).

  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. Intent row locks are not acquired if the SELECT statement uses an INTO clause since no positioned update can be performed.

  • FOR UPDATE BY { VALUES | TIMESTAMP } clause

    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.

• FOR JSON clause

  This clause specifies that the result set is to be returned in JSON format. The JSON format depends on the mode you specify. This clause cannot be used with the FOR UPDATE or FOR READ ONLY clause. Cursors declared with FOR JSON are implicitly READ ONLY.

  When you specify RAW mode, each row in the result set is returned as a flattened JSON representation.

  AUTO mode returns the query results as nested JSON objects based on query joins.

  EXPLICIT mode allows you to control the form of the generated JSON objects. Using EXPLICIT mode offers more flexibility in specifying columns and nested hierarchical objects to produce uniform or heterogeneous arrays.

• 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), then 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:

    • isolation_level option
    • max_query_tasks option
    • optimization_goal option
    • optimization_level option
    • optimization_workload option
    • user_estimates option
    • parameterization_level

    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.

The 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.

You must have the appropriate SELECT privileges on the objects referred to in the SELECT statement.

To select the CURRVAL or NEXTVAL values from a sequence generator, you must have USE ANY SEQUENCE system privilege, or be the owner of the sequence, or have been granted the required privileges to use the sequence generator.

None.

• ANSI/ISO SQL Standard

  Core feature. Check individual clauses against the standard. For example, the ROLLUP keyword, which can be specified in a GROUP BY clause, is part of optional ANSI/ISO SQL Language Feature T431. Some of the optional ANSI/ISO SQL Language Features supported by the software include:

  • The WINDOW clause and WINDOW aggregate functions comprise optional ANSI/ISO SQL Language Features T611 and T612.

  • Sequence expressions are part of ANSI/ISO SQL Feature T176.

  • Common table expressions are optional ANSI/ISO SQL Language Feature T121. A common table expression included in a nested query expression is Feature T122. WITH RECURSIVE is optional ANSI/ISO SQL 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 ANSI/ISO SQL Feature F850. The ability to specify ORDER BY in a subquery is ANSI/ISO SQL Feature F851.

  • In the ANSI/ISO SQL Standard, FOR UPDATE and FOR READ ONLY are part of a cursor declaration.

  The software offers support for many extensions to the ANSI/ISO SQL definition of the SELECT statement. Some of these include:

  • The optional cursor-concurrency clause (FOR UPDATE BY { LOCK | VALUES | TIMESTAMP}) is not part of the ANSI/ISO SQL Standard.

  • The FOR XML, OPTION, and INTO clauses are not part of the ANSI/ISO SQL Standard.

  • The row limitation clause is not part of the ANSI/ISO SQL Standard. In the ANSI/ISO SQL Standard, row limitation is supported using FETCH FIRST syntax, which is optional Language Feature F856. The syntax for Feature F856 is not supported by the software.

  • The ability to specify ORDER BY n is not part of the ANSI/ISO SQL Standard.

  • In the ANSI/ISO SQL Standard, all cursors except INSENSITIVE cursors are updatable by default. The read-only default with Embedded SQL programs is not part of the ANSI/ISO SQL Standard.

• 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:

  • SAP 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.