SELECT statement

The SELECT statement is the form of a query that can be directly specified in a DECLARE CURSOR statement, or prepared and then referenced in a DECLARE CURSOR statement. It can also be issued interactively using SPUFI or the Db2 command line processor, which causes a result table to be displayed at your terminal. In any case, the table specified by select-statement is the result of the fullselect.

Authorization for SELECT statements

For any form of a query, the privilege set that is defined below must include one of the following authorities or privilege sets:

For each table or view identified in the statement, the privilege set must include one of the following:
- Ownership of the table or view
- The SELECT privilege on the table or view
- DBADM authority for the database (tables only)
- Required privileges such as SELECT for each table or view and EXECUTE for each function that is identified in the fullselect of the CREATE VIEW statement (views only).
If the database is implicitly created, the database privileges must be on the implicit database or on DSNDB04.
SYSADM authority
SYSCTRL authority (catalog tables only)
DATAACCESS authority
Installation SYSOPR authority (catalog tables and system tables only)

If a query includes a user-defined function, the privileges that are held by the authorization ID of the statement must include at least one of the following:

For each user-defined function that is identified in the statement, one of the following:
- The EXECUTE privilege on the function
- Ownership of the function
SYSADM authority
DATAACCESS authority

If a query includes a sequence object, the privileges that are held by the authorization ID of the statement must include at least one of the following:

For each sequence object that is identified in the statement, one of the following:
- The USAGE privilege on the sequence object
- Ownership of the sequence object
DATAACCESS authority

If the select-statement is part of a DECLARE CURSOR statement, the privilege set is the privileges that are held by the authorization ID of the owner of the plan or package.

If the select-statement contains an SQL data change statement, the privilege set must include the SELECT privilege and the appropriate privileges for the SQL data change statement (insert, update, or delete privileges) on the target table or view.

If the select-statement references a table that contain an active row or column access control, and row permissions or column masks are defined for the table, the authorization ID or role of the statement does not need authority to reference objects that are specified in the definitions of those row permissions or column masks.

Privilege sets for queries

If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the plan or package.

If the statement is dynamically prepared, and the application is bound in a trusted context, the privilege set is the set of privileges that are held by that role. Otherwise, the privilege set depends on the dynamic SQL statement behavior, which is specified by option DYNAMICRULES:

Run behavior: The privilege set is the union of the privilege sets that are held by each authorization ID of the process.
Bind behavior: The privilege set is the privileges that are held by the authorization ID of the owner of the plan or package.
Define behavior: The privilege set is the privileges that are held by the authorization ID of the owner of the stored procedure or user-defined function.
Invoke behavior: The privilege set is the privileges that are held by the authorization ID of the invoker of the stored procedure or user-defined function.

For a list of the DYNAMICRULES values that specify run, bind, define, or invoke behavior, see Table 1.

When any form of a query is used as a component of another statement, the authorization rules that apply to the query are specified in the description of that statement. For example, see the authorization rules that apply to the subselect in a CREATE VIEW statement in CREATE VIEW statement.

If your installation uses the access control authorization exit (DSNX@XAC), that exit might be controlling the authorization rules instead of the rules that are listed here.

Syntax for `select-statement`

Notes:

¹ If fullselect is a VALUES clause, common-table-expression, update-clause, read-only-clause, optimize-clause, isolation-clause, queryno-clause, and SKIP LOCKED DATA must not also be specified
² The read-only-clause must not be specified if update-clause is specified.
³ The same clause must not be specified more than one time.

The select-statement is the form of a query that can be directly specified in a DECLARE CURSOR statement or FOR statement, prepared and then referenced in a DECLARE CURSOR statement, or directly specified in an SQLJ assignment clause. It can also be issued using SPUFI or the Db2 command line processor, which causes a result table to be displayed at your terminal. In any case, the result table specified by a select-statement is the result of the fullselect. For more information, see select-statement.

The tables and view identified in a select statement can be at the current server or any Db2 subsystem with which the current server can establish a connection.

For local queries on Db2 for z/OS® or remote queries in which the server and requester are Db2 for z/OS, if a table is encoded as ASCII or Unicode, the retrieved data is encoded in EBCDIC. For information on retrieving data encoded in ASCII or Unicode, see Distributed queries against ASCII or Unicode tables.

A select statement can implicitly or explicitly invoke user-defined functions or implicitly invoke stored procedures. This technique is known as nesting of SQL statements. A function or procedure is implicitly invoked in a select statement when it is invoked at a lower level. For instance, if you invoke a user-defined function from a select statement and the user-defined function invokes a stored procedure, you are implicitly invoking the stored procedure.

Syntax for `fullselect`

values-clause

Notes:

¹ If values-clause is specified, UNION, EXCEPT, INTERSECT, order-by-clause, or fetch-clause must not also be specified. If fullselect contains a values-clause, the fullselect must only be specified in a select-statement that is referenced by statement-name in a PREPARE statement.

The fullselect is a component of the select-statement, ALTER TABLE statement for the definition of a materialized query table, CREATE TABLE statement, CREATE VIEW statement, DECLARE GLOBAL TEMPORARY TABLE statement, INSERT statement, UPDATE statement, and MERGE statement. For more information, see fullselect

Syntax for `subselect`

The subselect is a component of the fullselect. A subselect specifies a result table that is derived from the tables or views that are identified in the FROM clause. For more information, see subselect.

Syntax for `select-clause`

The SELECT clause specifies the columns of the final result table. The column values are produced by the application of the select list to R. The select list is a list of names and expressions specified in the SELECT clause, and R is the result of the previous operation of the subselect. For example, if SELECT, FROM, and WHERE are the only clauses specified, then R is the result of that WHERE clause. For more information, see select-clause.

Syntax for `from-clause`

The FROM clause specifies an intermediate result table. for more information, see from-clause.

Syntax for `where-clause`

The WHERE clause specifies a result table that consists of those rows of R for which the search condition is true. R is the result of the FROM clause of the subselect. For more information, see where-clause.

Syntax for `group-by-clause`

The GROUP BY clause specifies a result table that consists of a grouping of the rows of intermediate result table that is the result of the previous clause. For more information, see group-by-clause.

Syntax for `having-clause`

The HAVING clause specifies a result table that consists of those groups of the intermediate result table for which the search-condition is true. The intermediate result table is the result of the previous clause. If this clause is not GROUP BY, the intermediate result table is considered a single group with no grouping columns of the previous clause of the subselect. For more information, see having-clause.

Syntax for `order-by-clause`

sort-key

The ORDER BY clause specifies an ordering of the rows of the result table. For more information, see order-by-clause.

Syntax for `fetch-clause`

The fetch-clause limits the number of rows that can be fetched. For more information, see fetch-clause.

Syntax for `offset-clause`

The offset-clause specifies the number of rows of the result table to skip before any rows are retrieved. For more information, see offset-clause.

Examples for SELECT

Example 1: Select all the rows from DSN8D10.EMP.
```
   SELECT * FROM DSN8D10.EMP;
```
Example 2: Select all the rows from DSN8D10.EMP, arranging the result table in chronological order by date of hiring.
```
   SELECT * FROM DSN8D10.EMP
      ORDER BY HIREDATE;
```
Example 3: Select the department number (WORKDEPT) and average departmental salary (SALARY) for all departments in the table DSN8D10.EMP. Arrange the result table in ascending order by average departmental salary.
```
   SELECT WORKDEPT, AVG(SALARY)
     FROM DSN8D10.EMP
     GROUP BY WORKDEPT
     ORDER BY 2;
```
Example 4: Change various salaries, bonuses, and commissions in the table DSN8D10.EMP. Confine the changes to employees in departments D11 and D21. Use positioned updates to do this with a cursor named UP_CUR. Use a FOR UPDATE clause in the cursor declaration to indicate that all updatable columns are updated. Below is the declaration for a PL/I program.
```
   EXEC SQL DECLARE UP_CUR CURSOR FOR
     SELECT WORKDEPT, EMPNO, SALARY, BONUS, COMM
       FROM DSN8D10.EMP
       WHERE WORKDEPT IN ('D11','D21')
       FOR UPDATE;
```
Beginning where the cursor is declared, all updatable columns would be updated. If only specific columns needed to be updated, such as only the salary column, the FOR UPDATE clause could be used to specify the salary column (FOR UPDATE OF SALARY).
Example 5: Find the maximum, minimum, and average bonus in the table DSN8D10.EMP. Execute the statement with uncommitted read isolation, regardless of the value of ISOLATION with which the plan or package containing the statement is bound. Assign 13 as the query number for the SELECT statement.
```
   EXEC SQL
     SELECT MAX(BONUS), MIN(BONUS), AVG(BONUS)
       INTO :MAX, :MIN, :AVG
       FROM DSN8D10.EMP
       WITH UR
       QUERYNO 13;
```
If bind option EXPLAIN(YES) is specified, rows are inserted into the plan table. The value used for the QUERYNO column for these rows is 13.
Example 6: The cursor declaration shown below is in a PL/I program. In the query within the declaration, X.RMT_TAB is an alias for a table at some other Db2. Hence, when the query is used, it is processed using DRDA access. See Distributed relational databases.

The declaration indicates that no positioned updates or deletes will be done with the query's cursor. It also specifies that the access path for the query be optimized for the retrieval of at most 50 rows. Even so, the program can retrieve more than 50 rows from the result table, which consists of the entire table identified by the alias. However, when more than 50 rows are retrieved, performance could possibly degrade.
```
   EXEC SQL DECLARE C1 CURSOR FOR
     SELECT * FROM X.RMT_TAB
     OPTIMIZE FOR 50 ROWS
     FOR READ ONLY;
```
The FETCH FIRST clause could be used instead of the OPTIMIZE FOR clause to ensure that only 50 rows are retrieved as in the following example:
```
   EXEC SQL DECLARE C1 CURSOR FOR
     SELECT * FROM X.RMT_TAB
     FETCH FIRST 50 ROWS ONLY;
```
Example 7: Assume that table DSN8D10.EMP has 1000 rows and you want to see the first five EMP_ROWID values that were inserted into DSN8D10.EMP_PHOTO_RESUME.
```
   EXEC SQL DECLARE CS1 CURSOR FOR
     SELECT EMP_ROWID
       FROM FINAL TABLE (INSERT INTO DSN8D10.EMP_PHOTO_RESUME (EMPNO)
                        SELECT EMPNO FROM DSN8D10.EMP)
       FETCH FIRST 5 ROWS ONLY;
```
All 1000 rows are inserted into DSN8D10.EMP_PHOTO_RESUME, but only the first five are returned.

SELECT statement

Authorization for SELECT statements

Privilege sets for queries

Syntax for select-statement

Syntax for fullselect

Syntax for subselect

Syntax for select-clause

Syntax for from-clause

Syntax for where-clause

Syntax for group-by-clause

Syntax for having-clause

Syntax for order-by-clause