subselect

The subselect is a component of the fullselect.

Read syntax diagramSkip visual syntax diagramselect-clausefrom-clausewhere-clausegroup-by-clausehaving-clause order-by-clause offset-clause fetch-clause isolation-clause

A subselect specifies a result table derived from the tables, views or nicknames 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 can 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 might or might not be executed.)

The authorization for a subselect is described in the Authorization section in "SQL queries".

The clauses of the subselect are processed in the following sequence:
  1. FROM clause
  2. WHERE clause
  3. GROUP BY clause
  4. HAVING clause
  5. SELECT clause
  6. ORDER BY clause
  7. OFFSET clause
  8. FETCH clause
A subselect that contains an ORDER BY clause, OFFSET clause, or FETCH clause cannot be specified:
  • In the outermost fullselect of a view.
  • In the outer fullselect of a materialized query table.
  • Unless the subselect is enclosed in parenthesis.
For example, the following is not valid (SQLSTATE 428FJ):
SELECT * FROM T1
   ORDER BY C1
UNION
SELECT * FROM T2
   ORDER BY C1
The following example is valid:
(SELECT * FROM T1
   ORDER BY C1)
UNION
(SELECT * FROM T2
   ORDER BY C1)
Note: An ORDER BY clause in a subselect does not affect the order of the rows returned by a query. An ORDER BY clause only affects the order of the rows returned if it is specified in the outermost fullselect.
For details about the clauses in the subselect query, refer to the following topics: