SELECT is an executable command that can be issued interactively or embedded in a host language. SELECT can be dynamically prepared.
In order to select data from a table or view, users must possess either SELECT authority on the table or view accessed, DBADM authority on the database, SYSADM or SYSCTRL authority for the location, or be the owner of the table or view.
SELECT command statements can be composed of six different types of clauses. The SELECT clause and the FROM clause are required for all SELECT commands.
WHERE clauses are permitted in all SELECT commands. You can force the use of an index for a particular indexed column or force the system to not use an index in the query. To force the use of an index when constructing the query, enter an asterisk (*) in parentheses after the name of the indexed column in the WHERE clause.
To force the system to ignore the index, enter a hyphen (-) in parentheses after the name of the indexed column. The (*) and (-) options are valid only for column names that appear in a WHERE clause. You cannot use the (*) and (-) options in GROUP BY, HAVING or ORDER BY clauses (see SELECT Clause for examples).
Depending on the other clauses they contain, SELECT commands may be grouped into the following three classes:
A subselect specifies a result table derived from the tables or views identified in the FROM clause of the query.
The subselect syntax appears below:
select-clause from-clause [where-clause] [group-by-clause] [having-clause]
| select-clause | Required component of a subselect. See SELECT Clause Syntax and Description for a complete syntax analysis of SELECT clauses. |
| from-clause | Required component of a subselect. See FROM Clause Syntax and Description for a complete syntax analysis of FROM clauses. |
| where-clause | Optional clause of a subselect. See WHERE Clause Syntax and Description for a complete syntax analysis of WHERE clauses. |
| group-by-clause | Optional clause of a subselect. See GROUP BY Clause for a complete syntax analysis of GROUP BY clauses. |
| having-clause | Optional clause of a subselect. See GROUP BY Clause for a complete syntax analysis of HAVING clauses. |
In addition to standing on its own, a subselect can be a component of the fullselect or incorporated into CREATE TABLE, UPDATE, INSERT, DELETE, and CREATE VIEW statements. Subselects can also be components of certain predicates, in which case they can be called subqueries. Subselects can contain GROUP BY or HAVING clauses, and the WHERE clause of a subselect can also contain nested subselects. Subselects cannot contain the UNION operator or an ORDER BY clause.
The subselect cannot reference a location other than the one referenced elsewhere in the command. That is, a referenced table and all subselects in a single SQL statement must be from the same location.
Invalid:
SELECT * FROM tutorial.tutorial.employeeWHERE e_no IN SELECT e_no FROM debloc.debbie.employee WHERE st = "MD";
Valid:
SELECT * FROM tutorial.tutorial.employeeWHERE e_no IN SELECT e_no FROM tutorial.debbie.employee WHERE st = "MD";
A fullselect can consist of one or more subselects connected with one or more UNION operators. Fullselects can also be issued from SQLWizard -- causing a result table to be displayed. See SELECT Clause for examples of fullselect queries.
The fullselect syntax appears below:
subselect [(fullselect)] [UNION | UNION ALL] [subselect [(fullselect)]]
A fullselect that contains no UNION or UNION ALL clauses is simply a subselect. A fullselect can combine multiple subselects (and fullselects) by repeated use of the UNION and UNION ALL keywords. Only the final query in a fullselect ends with a semicolon. The absence of semicolons after the preceding queries is what makes SQL cognizant that these queries are part of a UNION.
Combines the results output of columns from two separate tables as though they were one. The column headings are omitted in the result because no columns produced by a union are directly extracted from a single table. In order for two or more queries to undergo a union, their output columns must be union compatible. This means that the queries must each specify the same number of columns and in such an order that the first, second, third, and so on, of each is of a compatible data type with the first, second, third, and so on, of all the others.
Same as UNION, but includes all duplicate data rows in the output (UNION eliminates all redundant combinations of data values, therefore making every row of result data unique).
The AS clause affects column names in the results of fullselects. If the nth result columns of two queries combined in a UNION have the same result column name (defined with an AS clause), then the nth column of the final UNION result has the same defined column name.
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.
The select statement syntax appears below:
fullselect [order-by-clause] [update-clause]
{[FOR FETCH ONLY] [FOR READ ONLY]}
[optimize-for-clause]
[with-clause]
[queryno-clause]
[fetch-first-clause]
order-by-clause:
ORDER BY {column-name | integer}
{ASC | DESC}[,...]
update-clause:
FOR UPDATE OF column-name[,...]
optimize-for-clause:
OPTIMIZE FOR integer {ROWS | ROW}
with-clause:
WITH {CS | RR | UR}
queryno-clause:
QUERYNO integer
fetch-first-clause:
FETCH FIRST {1 | integer} {ROW | ROWS} ONLY
| column-name | Uniquely identifies a column of the result table. A named column may be identified by an integer or column-name. An unnamed column must be identified by an integer (below). |
| integer | An integer value greater than zero, and not greater than the number of columns in the result table. The integer n identifies the nth column of the result table. |
The table specified by the select statement is the result of a fullselect. The tables or views identified can reside in the currently set location or at any other accessible location under the XDB Server. To access other locations, specify a three-part name (or alias) or issue a CONNECT or SET LOCATION TO command prior to issuing the select statement.
Including this clause or the FOR READ ONLY clause (in DB2 Version 4.1), declares that the result table is read-only. A read-only result table must not be referred to by any UPDATE or DELETE statements. These limitations apply to result tables that are intrinsically read-only (such as those based on read-only views), and those for which the FOR FETCH ONLY or FOR READ ONLY clause is specified by the user.
Cursors with read-only result tables cannot be referred to in positioned UPDATE and DELETE statements, since data is fetched between client and server in large blocks. However, if no positional updates are needed, the FOR FETCH ONLY clause can still be used to improve fetch performance.
Specifying FOR FETCH ONLY activates Bulk Fetching, which improves the performance of FETCH operations. When a query is Bulk Fetched, data is transferred to the client in bulk, instead of one row at a time. The bulk size depends on the number of records that can be transferred, which in turn is influenced by the number of columns selected, data types and the overhead.
This clause specifies an order for result table rows. If one column-name is identified, the rows are ordered by the data values within the column. If more than one column-name is identified, the rows are ordered by the values of the first column, then by the values of the second column, etc. ASC uses the values of the identified column in ascending order (default), while DESC uses the values of the column in descending order. If the ORDER BY clause is not specified, the rows of the result table have an arbitrary order.
The inclusion of an AS clause in the fullselect allows a named column to be identified by a column name. An unnamed column must be identified by an integer. A column is unnamed if the AS clause is not specified and it is derived from a constant, an expression with operators, or a function. If the fullselect includes a UNION operator, the fullselect rules on named columns apply.
The UPDATE clause identifies the columns that can be updated in a later positioned UPDATE statement. The FOR UPDATE OF clause restricts the table columns that can be updated to those specified in the column-name list. These column names must be defined for the table or view appearing in the first FROM clause of the fullselect, and must not be qualified. FOR UPDATE OF cannot be used if the result table is read-only, nor can it be used in a statement containing a FOR FETCH ONLY clause.
This optional clause can be placed either before or after any accompanying FOR FETCH ONLY clause. OPTIMIZE FOR tells the XDB Server to proceed under the assumption that "at most a total of integer rows are to be retrieved from the result table." Without this clause, the XDB Server will assume that all rows of the result table are to be retrieved (and optimized accordingly).
OPTIMIZE FOR does not actually affect the composition or ordering of the result table. Optimizing for a limited (integer) number of rows can improve fetch performance if subsequent fetches are limited to integer rows. Any number of rows can be fetched after using the OPTIMIZE FOR clause, but after a total of integer rows are fetched, subsequent fetch performance may degrade.
You can override the isolation level for all SQL statements specified in a statement with the ISOLATION option by using the WITH clause on individual SQL statements. The WITH clause overrides the default isolation level only for the statement in which it appears. The WITH clause cannot be used on subselects.
The following three isolation levels can be specified:
| CS | Cursor stability |
| RR | Repeatable read |
| UR | Uncommitted read |
WITH UR can be specified only after SELECT, and only if the result table is read-only. A simple way to assure that a result table is read-only is to specify FOR FETCH ONLY or FOR READ ONLY. More information on isolation levels can be found in the Server Administration Guide.
The FETCH FIRST clause limits the number of rows that can be fetched. It can improve the performance of queries with potentially large result sets when only a limited number of rows are needed. If the clause is specified, the number of rows retrieved will not exceed n, where n is the value of the integer. An attempt to fetch n+1 rows is handled the same way as normal end of data. The value of integer must be positive and non-zero. The default is 1.
If the OPTIMIZE FOR clause is not specified, a default of "OPTIMIZE FOR integer ROWS" is assumed. If both the FETCH FIRST and OPTIMIZE FOR clauses are specified, the lower of the integer values from these clauses is used to influence optimization and the communications buffer size.
If both the FETCH FIRST clause and the ORDER BY clause are specified, the ordering is performed on the entire result set prior to returning the first n rows.
Due to the relative importance and complexity of SELECT commands (and supporting clauses), this command is described in detail (including numerous examples) in the following topics: