subselect
The subselect is a component of the fullselect.
>>-select-clause--from-clause--+--------------+-----------------> '-where-clause-' >--+---------------------------+--+-----------------+-----------> '-hierarchical-query-clause-' '-group-by-clause-' >--+---------------+--+-----------------+--+--------------------+->< '-having-clause-' '-order-by-clause-' '-fetch-first-clause-'
A subselect specifies a result table derived from the tables or views identified in the FROM clause. The derivation can be described as a sequence of operations in which the result of each operation is input for the next. (This is only a way of describing the subselect. The method used to perform the derivation may be quite different from this description. If portions of the subselect do not actually need to be executed for the correct result to be obtained, they may or may not be executed.)
A scalar-subselect is a subselect, enclosed in parentheses, that returns a single result row and a single result column. If the result of the subselect is no rows, then the null value is returned. An error is returned if there is more than one row in the result.
The sequence of the (hypothetical) operations is:
- FROM clause
hierarchical-query clause
- WHERE clause
- GROUP BY clause
- HAVING clause
- SELECT clause
- ORDER BY clause
- FETCH FIRST clause
- select-clause
The SELECT clause specifies the columns of the final result table. - from-clause
The FROM clause specifies an intermediate result table. - Hierarchical queries
Hierarchical queries are a form of recursive query that provides support for retrieving a hierarchy, such as a bill of materials, from relational data using a CONNECT BY clause. - where-clause
The WHERE clause specifies an intermediate 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 statement. - group-by-clause
The GROUP BY clause specifies an intermediate result table that consists of a grouping of the rows of R. R is the result of the previous clause of the subselect. - having-clause
The HAVING clause specifies an intermediate result table that consists of those groups of R for which the search-condition is true. R is the result of the previous clause of the subselect. If this clause is not GROUP BY, R is considered a single group with no grouping expressions. - order-by-clause
The ORDER BY clause specifies an ordering of the rows of the result table. - offset-clause
The offset-clause sets the number of rows to skip before any rows are retrieved. It lets the database manager know that the application does not want to start retrieving rows until offset-row-count rows have been skipped. If offset-clause is not specified, the default is equivalent to OFFSET 0 ROWS. An attempt to skip more rows than the number of rows in the intermediate result table is handled the same way as an empty result table. - fetch-first-clause
The fetch-first-clause sets a maximum number of rows that can be retrieved. It lets the database manager know that the application does not want to retrieve more than fetch-first-row-count rows, regardless of how many rows there are in the intermediate result table. An attempt to fetch beyond fetch-first-row-count rows is handled the same way as normal end of data. - Examples of a subselect
subselect can be used in many different ways.