7.3.1 Specification format and rules for subqueries

A subquery is an inner query specification. There are the following two types of subqueries:

Scalar subquery

This is a subquery for which the result of the query is at most a single row containing a single column (a single value).
Table subquery

This is a subquery for which the result of the query is zero or more rows containing one or more columns.

Organization of this subsection

(1) Specification format
(2) Explanation of specification format
(3) Privileges required at execution
(4) Rules
(5) Examples
(6) Notes

(1) Specification format

subquery::=([subquery-processing-method-specification] query-expression-body [LIMIT-clause])
 
  subquery-processing-method-specification::=/*>> SUBQUERY NOT BY HASH[subquery-processing-delegation-specification]<<*/
    subquery-processing-delegation-specification::=(DELEGATION)

To Page Top

(2) Explanation of specification format

subquery-processing-method-specification:

If a subquery processing method specification is used, a processing method other than hash execution is used as the method for processing the subquery. For details about the method for processing the subquery, see Subquery processing methods in the HADB Application Development Guide.

Note that, normally, there is no need to specify this. If the subquery processing method specification is omitted, HADB determines the method for processing the subquery.

Also note that a subquery processing method specification cannot be used for the following items:

Table subquery for a derived table
Table subquery for a multiset value constructor by query

subquery-processing-delegation-specification:

If a subquery processing delegation specification is used, SQL processing real threads that are used for other processing can be assigned to the search processing of a subquery that includes external reference columns.

Note that, normally, there is no need to specify this. If much search processing is required to obtain the results of a subquery that includes external reference columns, consider using a subquery processing delegation specification.

Example

SELECT COUNT(*) FROM "T1" WHERE "T1"."C1" = ANY(
  SELECT "T2"."C1" FROM "T2" WHERE "T1"."C2" = "T2"."C2")

In the preceding SQL statement, table T2 is searched each time a result of table T1 is obtained. If table T2 that satisfies the search condition "T1"."C2" = "T2"."C2" has many rows, the search performance might be improved by using a subquery processing delegation specification. However, if a subquery processing delegation specification is used, the processing that assigns other SQL processing real threads to the search processing of a subquery that includes external reference columns becomes an overhead. Therefore, the search performance might be lowered depending on the search conditions.

The following shows the rules that apply when a subquery processing delegation specification is used:

A subquery processing delegation specification is ignored if it is used for a subquery that satisfies either of the following conditions:
- Subquery that does not include external reference columns
- Subquery that is specified in an SQL statement for which out-of-order execution is not used
Example of when a subquery processing delegation specification is ignored:

In the preceding example, a subquery processing delegation specification is included in a subquery that does not include an external reference column. However, no subquery processing delegation specification is included in a subquery that includes an external reference column. In this case, because no subquery processing delegation specification is included in a subquery that includes an external reference column, the subquery processing delegation specification is ignored.
If there are nested subqueries that include external reference columns and at least one of the nested subqueries has a subquery processing delegation specification, all the nested subqueries are treated as those having a subquery processing delegation specification. The following shows an example of an SQL statement that contains nested subqueries that include an external reference column. In this example, two subqueries that include an external reference column are specified, and only one of them has a subquery processing delegation specification. In this case, both subqueries are assumed to have a subquery processing delegation specification.
If there are subqueries that reference the same table as external reference columns and at least one of them has a subquery processing delegation specification, the other subqueries are also assumed to have a subquery processing delegation specification. The following shows an example. In this example, the SQL statement contains two subqueries that reference the same table as an external reference column, and only one of them has a subquery processing delegation specification. In this case, both subqueries are assumed to have a subquery processing delegation specification.

query-expression-body:

For details about query-expression-body, see (2) Explanation of specification format in 7.1.1 Specification format and rules for query expressions.

LIMIT-clause:

Specifies the maximum number of rows that will be retrieved from the results of the query expression body.

For details about the LIMIT clause, see 7.9 LIMIT clause.

The LIMIT clause can only be specified for a derived table returned by a table subquery, or in a scalar subquery. However, the following is a case of a derived table where a LIMIT clause is not permitted:

A derived table that references a table that is outside the derived table in which the LIMIT clause is specified

Example of an SQL statement that generates an error:

In this example, "T1"."C1" references a table that is outside the derived table in which the LIMIT clause is specified (correlation name: DRV). The LIMIT clause is therefore not permitted here.

For details about derived tables, see 7.11.1 Specification format for table references.

To Page Top

(3) Privileges required at execution

To execute a subquery, the SELECT privilege is required on all tables referenced in the subquery.

To Page Top

(4) Rules

(a) Common rules for subqueries

The data type of the result of the subquery will the same as the data type of the result of the query expression body.
For details about the column names of tables derived in the results of subqueries, see 6.9 Derived column names.
The following cannot be specified in a selection expression in a subquery:
- An external reference column
- [table-specification.]ROW
■ External reference column

A reference in the search conditions of a subquery to a table specified in the FROM clause of the outer query is known as an external reference. A corresponding referenced column is called an external reference column. An example of an external reference column is shown in the following figure.

Figure 7‒2: External reference column

Subqueries can be nested to a maximum of 32 levels deep (31 in the case of a view definition or a WITH clause). In addition, the following rules apply:

If the table specified in the FROM clause is a viewed table or query name

After HADB generates the internal derived table to the viewed table or query name, the subquery nesting depth must not exceed 32 (31 in the case of a view definition or a WITH clause).
If the table specified in the FROM clause is a recursive query name that references a recursive query that includes the table specified in the FROM clause

After HADB generates the internal derived table that corresponds to the recursive query name once, the subquery nesting depth must not exceed 32 (31 in the case of a view definition or a WITH clause).
If an archivable multi-chunk table is specified in the FROM clause

The archivable multi-chunk table is equivalently exchanged into an internal derived table. If the subquery nesting depth becomes 33 or more after equivalent exchange into an internal derived table, the SQL statement results in an error. For details about equivalent exchange of archivable multi-chunk tables, see Equivalent exchange of SQL statements that search archivable multi-chunk tables in the HADB Application Development Guide.
If the derived table derived by a table value constructor is specified in the FROM clause

The subquery nesting depth increases by 1.

The following shows examples of counting the subquery nesting depth.

Example 1:

In the following example, the SELECT statement includes subqueries that are nested to a depth of eight.

SELECT * FROM "TT" WHERE EXISTS(
    SELECT * FROM "T0" WHERE EXISTS(
    SELECT * FROM "T1" WHERE EXISTS(     <= 1st nest
    SELECT * FROM "T2" WHERE EXISTS(     <= 2nd nest
    SELECT * FROM "T3" WHERE EXISTS(     <= 3rd nest
    SELECT * FROM "T4" WHERE EXISTS(     <= 4th nest
    SELECT * FROM "T5" WHERE EXISTS(     <= 5th nest
    SELECT * FROM "T6" WHERE EXISTS(     <= 6th nest
    SELECT * FROM "T7" WHERE EXISTS(     <= 7th nest
    SELECT * FROM "T8"                   <= 8th nest
    )))))))))

Example 2:

In the following example, the CREATE VIEW statement includes subqueries that are nested to a depth of seven.

CREATE VIEW "V1" AS SELECT * FROM "TT" WHERE EXISTS(
    SELECT * FROM "T0" WHERE EXISTS(
    SELECT * FROM "T1" WHERE EXISTS(     <= 1st nest
    SELECT * FROM "T2" WHERE EXISTS(     <= 2nd nest
    SELECT * FROM "T3" WHERE EXISTS(     <= 3rd nest
    SELECT * FROM "T4" WHERE EXISTS(     <= 4th nest
    SELECT * FROM "T5" WHERE EXISTS(     <= 5th nest
    SELECT * FROM "T6" WHERE EXISTS(     <= 6th nest
    SELECT * FROM "T7"                   <= 7th nest
    ))))))))

When the following SELECT statement is run, an internal derived table is generated. As a result, the subquery nesting depth becomes eight.

SELECT * FROM "V1"

Example 3:

When the following SELECT statement is run, an internal derived table is generated. As a result, the subquery nesting depth becomes eight.

WITH "Q1" AS (SELECT * FROM "TT" WHERE EXISTS(
    SELECT * FROM "T0" WHERE EXISTS(
    SELECT * FROM "T1" WHERE EXISTS(     <= 1st nest
    SELECT * FROM "T2" WHERE EXISTS(     <= 2nd nest
    SELECT * FROM "T3" WHERE EXISTS(     <= 3rd nest
    SELECT * FROM "T4" WHERE EXISTS(     <= 4th nest
    SELECT * FROM "T5" WHERE EXISTS(     <= 5th nest
    SELECT * FROM "T6" WHERE EXISTS(     <= 6th nest
    SELECT * FROM "T7"                   <= 7th nest
    )))))))))
SELECT * FROM "Q1"                  <= 8th nest produced because an internal derived table was generated

Example 4:

In the following SELECT statement, recursive query name Q1 is specified in the FROM clause. When the following SELECT statement is run, an internal derived table is generated. As a result, the subquery nesting depth becomes eight.

SQL statement to be run

WITH "Q1" AS (SELECT "C1" FROM "TT"
              UNION ALL
              SELECT "C1"+1 FROM "Q1" WHERE "C1"+1 < 5)
    SELECT * FROM "TT" WHERE EXISTS(
    SELECT * FROM "T0" WHERE EXISTS(
    SELECT * FROM "T1" WHERE EXISTS(
    SELECT * FROM "T2" WHERE EXISTS(
    SELECT * FROM "T3" WHERE EXISTS(
    SELECT * FROM "T4" WHERE EXISTS(
    SELECT * FROM "T5" WHERE EXISTS(
    SELECT * FROM "Q1")))))))

SQL statement after an internal derived table is generated

SELECT * FROM "TT" WHERE EXISTS(
SELECT * FROM "T0" WHERE EXISTS(
SELECT * FROM "T1" WHERE EXISTS(     <= 1st nest
SELECT * FROM "T2" WHERE EXISTS(     <= 2nd nest
SELECT * FROM "T3" WHERE EXISTS(     <= 3rd nest
SELECT * FROM "T4" WHERE EXISTS(     <= 4th nest
SELECT * FROM "T5" WHERE EXISTS(     <= 5th nest
SELECT * FROM                        <= 6th nest
              (SELECT "C1" FROM "TT"                       <= 7th nest
               UNION ALL
               SELECT "C1"+1 FROM 
                                  (SELECT "C1" FROM "TT"   <= 8th nest
                                   UNION ALL
                                   SELECT "C1"+1 FROM "Q1" WHERE "C1"+1 < 5)"Q1"
               WHERE "C1"+1 < 5)"Q1")))))))

Example 5:

In the following example, the SELECT statement includes subqueries that are nested to a depth of seven. Also, because a table derived by a table value constructor is specified in the FROM clause, the subquery nesting depth is incremented by one. Therefore, in total, the nesting depth is assumed to be eight.

SELECT * FROM "TT" WHERE EXISTS(
    SELECT * FROM "T0" WHERE EXISTS(
    SELECT * FROM "T1" WHERE EXISTS(      <= 1st nest
    SELECT * FROM "T2" WHERE EXISTS(      <= 2nd nest
    SELECT * FROM "T3" WHERE EXISTS(      <= 3rd nest
    SELECT * FROM "T4" WHERE EXISTS(      <= 4th nest
    SELECT * FROM "T5" WHERE EXISTS(      <= 5th nest
    SELECT * FROM "T6" WHERE EXISTS(      <= 6th nest
    SELECT * FROM (VALUES (1,2,3)) "T7"   <= 7th and 8th nests
    ))))))))

Subqueries are not permitted in set functions.
Subqueries are not permitted in window functions.
Subqueries are not permitted in the grouping specification of a GROUP BY clause.
Subqueries are not permitted in the ON search condition of a joined table with FULL OUTER JOIN specified as the joined table mode.

You cannot specify a column that makes an external reference to a table reference in a joined table with FULL OUTER JOIN specified as the joined table mode.

Example: The underlined portion indicates an incorrect external reference column.

  SELECT * FROM ("T1" LEFT OUTER JOIN "T2" ON "T1"."C1"="T2"."C1")
                      FULL OUTER JOIN "T3" ON "T1"."C2"="T3"."C2"
      WHERE "T1"."C3">(SELECT MAX(C3) FROM "T4"
                          WHERE "C1"="T1"."C1"
                            AND "C2"="T3"."C2")

(b) Rules for scalar subqueries

A scalar subquery cannot return more than one column.
A scalar subquery cannot return more than one row. An SQL error results if there is more than one row in the results.
If the number of rows in the result of a scalar subquery is zero, the result is the null value.
The NOT NULL constraint does not apply to the result of a scalar subquery (the null value is allowed).

(c) Rules for table subqueries

The maximum number of columns permitted in the results of a table subquery are as follows.
- If the table subquery specifies a derived table: 1,000
- If the table subquery is specified in an IN predicate or on the right side of a quantified predicate: 1
- If the table subquery is specified in an EXISTS predicate: 1,000

To Page Top

(5) Examples

Example 1

This example retrieves the names (NAME) and salary (SAL) of the employees who earn the highest salary.

SELECT "NAME","SAL"
    FROM "SALARYLIST"
       WHERE "SAL"=(SELECT MAX("SAL") FROM "SALARYLIST")

The underlined portion indicates the subquery.

Example 2

This example finds the sections (SCODE) in which the average salary is greater than the average salary for all employees.

SELECT "SCODE",AVG("SAL")
    FROM "SALARYLIST"
      GROUP BY "SCODE"
      HAVING AVG("SAL")>(SELECT AVG("SAL") FROM "SALARYLIST")

The underlined portion indicates the subquery.

Example 3

This example retrieves 100 rows from the sales history table (SALESLIST), and then calculate the total quantity purchased (PUR-NUM) for each product code (PUR-CODE) in those results.

SELECT "PUR-CODE",SUM("PUR-NUM")
    FROM (SELECT * FROM "SALESLIST" LIMIT 100) "SALESLIST"
        GROUP BY "PUR-CODE"

The underlined portion indicates the subquery.

To Page Top

(6) Notes

When a subquery is specified, a work table might be created. If the size of the work table DB area where the work table is to be created has not been estimated correctly, performance might be degraded. For details about estimating the size of the work table DB area, see the HADB Setup and Operation Guide. For details about work tables, see Considerations when executing an SQL statement that creates work tables in the HADB Application Development Guide.
If hash execution is used during subquery processing, a hash table area of an appropriate size is required. The size of the hash table area is specified in the adb_sql_exe_hashtbl_area_size operand in the server definition or client definition. If hash execution is used as the method for processing the subquery, a hash filter area to store hash filters is also required. The size of the hash filter area is specified by using the adb_sql_exe_hashflt_area_size operand in the server definition or client definition. For details about the method for processing the subquery, see Subquery processing methods in the HADB Application Development Guide.

To Page Top