Show TOC

Background documentationSubquery (subquery)

 

A subquery (subquery) specifies a result table that can be used in certain predicates and for updating column values.

A scalar subquery (scalar_subquery) is a special subquery.

Structure

Syntax Syntax

  1. <subquery> ::= 
  (<query_expression>)

<scalar_subquery> ::=
  <subquery>
End of the code.
Examples

SQL Tutorial, Subquery: Inner Queries

Explanation

Subqueries can be used in a SET UPDATE clause of an UPDATE statement. In this case, the subquery must produce a result table that contains a maximum of one row. Subqueries can be used in an INSERT statement.

Subqueries can be used in the following predicates:

Correlated Subqueries

Certain predicates can contain subqueries. These subqueries, in turn, can contain other subqueries, and so on. A subquery with further subqueries is the higher-level subquery of the subqueries it contains.

  • The search condition of a subquery can contain column names belonging to tables that are contained in higher levels in the FROM clause. This type of subquery is called a correlated subquery.

  • Tables that are used in subqueries in such a way are called correlated tables. No more than 64 correlated tables are allowed within an SQL statement.

  • Columns that are used in subqueries in this way are called correlated columns. A total of 64 correlated columns can be used in an SQL statement.

If the qualifying table name or reference name does not uniquely identify a table at a higher level, then the lowest level table of these non-unique tables is taken.

If the column name is not qualified by the table name or reference name, the tables at higher levels are searched. The column name must be unique in all tables of the FROM clause to which the found table belongs.

If a correlated subquery is used, the values of one or more columns in a temporary result row at a higher level are included in the search condition of a subquery at a lower level, whereby the result of the subquery is used to uniquely qualify the higher-level temporary result row.

Scalar Subquery (scalar_subquery)

Scalar subqueries are produced through the restriction of the result set of a result table to a maximum of one value. Scalar subqueries can be used as expressions.

Scalar subqueries are not allowed in a GROUP clause or an ORDER clause (table expression).

Example Example

Scalar subquery in the list of values to be inserted in an INSERT statement:

INSERT INTO hotel.hotel

  VALUES((SELECT MAX(hno)+10 FROM hotel), 'Three Seasons', 90014, 'Los Angeles', '247 Broad Street')

Scalar subquery in a selected column:

SELECT hno, price, (SELECT MIN(price) FROM hotel.room)

  FROM hotel.room

End of the example.