Subqueries

A subquery is a SELECT statement that is nested within another or multiple SELECT statements. It can return a single-row result, multiple-row results, or no result. A subquery in the FROM clause of a SELECT statement is also known as an inline view. You can nest any number of subqueries in an embedded view. A subquery in the WHERE clause of a SELECT statement is also known as a nested subquery.

Subqueries can be classified into correlated subqueries and noncorrelated subqueries. A correlated subquery is executed multiple times, depending on the variables of the outer query. A noncorrelated subquery is executed only once and does not depend on the variables of the outer query. For noncorrelated subqueries and some correlated subqueries, you can rewrite the subqueries to eliminate the subqueries and expand the nested subqueries.

Subquery syntax

The syntax of a subquery is as follows:

SELECT [ hint ] [ { { DISTINCT | UNIQUE } | ALL } ] select_list
FROM { table_reference | join_clause | ( join_clause ) }
  [ , { table_reference | join_clause | (join_clause) } ]
  [ where_clause ]
  [ hierarchical_query_clause ]
  [ group_by_clause ]
| subquery { UNION [ALL] | INTERSECT | MINUS } subquery [ { UNION [ALL] | INTERSECT | MINUS } subquery ]
| ( subquery ) [ order_by_clause ] [ row_limiting_clause ]

The following table describes the parameters.

If a column in a subquery has the same name as a column in the outer query, you must specify the table name or an alias before the repeated column name in the outer query.

When an upper-level query references a correlated column in a subquery, the subquery is executed. The upper-level query can be a SELECT, UPDATE, or DELETE statement. The subquery can be used in the following ways in these statements:

• Define the row set to be inserted into the target table in an INSERT or CREATE TABLE statement.

• Define the row set to be included in the view in a CREATE VIEW or CREATE MATERIALIZED VIEW statement.

• Define one or more values to be assigned to existing rows in an UPDATE statement.

• Provide condition values in the WHERE, HAVING, or START WITH clauses.

• Define a table that contains a query operation.

Unnesting of nested subqueries

Unnesting of nested subqueries is an optimization strategy in a database. It moves some subqueries to the outer parent query and converts them into equivalent multi-table join operations. This strategy effectively utilizes access paths, join methods, and join order, reducing the query hierarchy as much as possible.

The database unnests nested subqueries in the following cases:

• Noncorrelated IN subqueries.

• Correlated IN and EXISTS subqueries that do not contain aggregate functions or GROUP BY clauses.

You can use the UNNEST hint to control whether to unnest nested subqueries.

Examples

The following statements create tables table_a and table_b and insert data into the tables.

CREATE TABLE table_a(PK INT, name VARCHAR(25));
INSERT INTO table_a VALUES(1,'Foxy');
INSERT INTO table_a VALUES(2,'Police');
INSERT INTO table_a VALUES(3,'Taxi');
INSERT INTO table_a VALUES(4,'Lincoln');
INSERT INTO table_a VALUES(5,'Arizona');
INSERT INTO table_a VALUES(6,'Washington');
INSERT INTO table_a VALUES(7,'Dell');
INSERT INTO table_a VALUES(10,'Lucent');
CREATE TABLE table_b(PK INT, name VARCHAR(25));
INSERT INTO table_b VALUES(1,'Foxy');
INSERT INTO table_b VALUES(2,'Police');
INSERT INTO table_b VALUES(3,'Taxi');
INSERT INTO table_b VALUES(6,'Washington');
INSERT INTO table_b VALUES(7,'Dell');
INSERT INTO table_b VALUES(8,'Microsoft');
INSERT INTO table_b VALUES(9,'Apple');
INSERT INTO table_b VALUES(11,'Scottish Whisky');

• A subquery that is not correlated

  obclient> SELECT * FROM TABLE_A T1 WHERE T1.PK IN (SELECT T2.PK FROM TABLE_B T2);
  +------+-----------+
  | PK   | NAME      |
  +------+-----------+
  |    1 | Foxy      |
  |    2 | Police    |
  |    3 | Taxi      |
  |    6 | Washington|
  |    7 | Dell      |
  +------+-----------+
  5 rows in set
  

• A correlated subquery that uses the outer query variable T1.PK

  SELECT * FROM TABLE_A T1 WHERE T1.PK IN (SELECT T2.PK FROM TABLE_B T2 WHERE T2.PK = T1.PK);
  +------+-----------+
  | PK   | NAME      |
  +------+-----------+
  |    1 | Foxy      |
  |    2 | Police    |
  |    3 | Taxi      |
  |    6 | Washington|
  |    7 | Dell      |
  +------+-----------+
  5 rows in set
  

• A correlated subquery that is rewritten as a join

  obclient> EXPLAIN SELECT * FROM TABLE_A T1
    WHERE T1.PK IN (SELECT T2.NAME FROM TABLE_B T2 WHERE T2.NAME = T1.NAME);
  +------------------------------------+
  | Query Plan                         |
  +------------------------------------+
  =============================================
  |ID|OPERATOR            |NAME|EST. ROWS|COST|
  ---------------------------------------------
  |0 |HASH RIGHT SEMI JOIN|    |8        |107 |
  |1 | TABLE SCAN         |T2  |8        |38  |
  |2 | TABLE SCAN         |T1  |8        |38  |
  =============================================
  Outputs & filters:
  -------------------------------------
    0 - output([T1.PK], [T1.NAME]), filter(nil),
        equal_conds([T1.PK = T2.NAME], [T2.NAME = T1.NAME]), other_conds(nil)
    1 - output([T2.NAME]), filter(nil),
        access([T2.NAME]), partitions(p0)
    2 - output([T1.NAME], [T1.PK]), filter(nil),
        access([T1.NAME], [T1.PK]), partitions(p0)
  +------------------------------------+
  1 row in set