5.15 SELECT

Description

Selects the specified column values from one or more rows contained in the table(s) specified in the FROM clause. The selection of rows is restricted by the WHERE clause. The temporary table derived through the clauses of a select statement is called a result table.

The format of the SELECT statement is a query expression with optional ORDER BY and FOR UPDATE clauses. For more detail on query expressions, see "Query Expressions".

Syntax

select_statement ::
    query_expression
    ORDER BY { expr | posn } [ COLLATE collation_name ] [ ASC | DESC ]
       [ , { expr | posn } [ COLLATE collation_name ] [ASC | DESC] ,... ]
    FOR UPDATE [ OF [table].column_name, ... ] [ NOWAIT ]
    ;

query_expression ::
    query_specification
|   query_expression set_operator query_expression
|   ( query_expression )

set_operator ::
     {  UNION [ ALL ]  |  INTERSECT  |  MINUS }

query_specification ::
SELECT [ALL | DISTINCT]
        {
           *
        |  { table_name | alias } . * [, { table_name | alias } . * ] …
        |  expr [ [ AS ] [ ' ] column_title [ ' ] ] [, expr [ [ AS ] [ ' ] column_title [ ' ] ] ] ...
        }
FROM table_ref [ { dharma ORDERED } ] [ , table_ref [ { dharma ORDERED } ] …
[ WHERE search_condition ]
[ GROUP BY [table.]column_name [ COLLATE collation-name ]
                  [, [table.]column_name [ COLLATE collation-name ] ] ...
[ HAVING search_condition ]

table_ref ::
           table_name [ AS ] [ alias [ ( column_alias [ , … ] ) ] ]
        |  ( query_expression ) [ AS ] alias [ ( column_alias [ , … ] ) ]
        |  [ ( ] joined_table [ ) ]

joined_table ::
           table_ref CROSS JOIN table_ref
        |  table_ref [ INNER | LEFT [ OUTER ] ] JOIN table_ref ON search_condition

Arguments

query_expression

See Section 4.4.

ORDER BY clause

See Section 5.15.1.

FOR UPDATE clause

See Section 5.15.2.

Authorization

The user executing this statement must have any of the following privileges:

• DBA privilege
• SELECT permission on all the tables/views referred to in the query_expression.

SQL Compliance

SQL-92. Extensions: FOR UPDATE clause. ODBC Extended SQL grammar.

Environment

Embedded SQL (within DECLARE), interactive SQL, ODBC applications

Related Statements

Query Expressions, DECLARE CURSOR, OPEN, FETCH, CLOSE

5.15.1 ORDER BY Clause

Description

The ORDER BY clause specifies the sorting of rows retrieved by the SELECT statement. SQL does not guarantee the sort order of rows unless the SELECT statement includes an ORDER BY clause.

Syntax

ORDER BY { expr | posn } [ COLLATE collation_name ] [ ASC | DESC ]
       [ , { expr | posn } [ COLLATE collation_name ] [ASC | DESC] ,... ]

Notes

• Ascending order is the default ordering. The descending order will be used only if the keyword DESC is specified for that column.
• Each expr is an expression of one or more columns of the tables specified in the FROM clause of the SELECT statement. Each posn is a number identifying the column position of the columns being selected by the SELECT statement.
• The selected rows are ordered on the basis of the first expr or posn and if the values are the same then the second expr or posn is used in the ordering.
• The ORDER BY clause if specified should follow all other clauses of the SELECT statement.
• A query expression followed by an optional ORDER BY clause can be specified. In such a case, if the query expression contains set operators, then the ORDER BY clause can specify only the positions. For example:

-- Get a merged list of customers and suppliers
-- sorted by their name.
     (SELECT name, street, state, zip
     FROM customer
     UNION
     SELECT name, street, state, zip
     FROM supplier)
     ORDER BY 1 ;

• If expr or posn refers to a character column, the reference can include an optional COLLATE clause. The COLLATE clause specifies a collation sequence supported by the underlying storage system. (See "Specifying the Character Set for Character Data Types" on page 4-6 for notes on character sets and collations. See the documentation for your underlying storage system for details on any supported collations.)

Example

SELECT name, street, city, state, zip
     FROM customer
     ORDER BY name ;

5.15.2 FOR UPDATE Clause

Description

The FOR UPDATE clause specifies update intention on the rows selected by the SELECT statement.

Syntax

FOR UPDATE [ OF [table].column_name, ... ] [ NOWAIT ]Notes

• If FOR UPDATE clause is specified, WRITE locks are acquired on all the rows selected by the SELECT statement.
• If NOWAIT is specified, an error is returned when a lock cannot be acquired on a row in the selection set because of the lock held by some other transaction. Otherwise, the transaction would wait until it gets the required lock or until it times out waiting for the lock.