Subqueries

A subquery is one or more SELECT statements that are nested in a SELECT query statement. A subquery can return a row, multiple rows, or no result. A subquery in the FROM clause of a SELECT statement is called an inline view. A subquery in the WHERE clause of a SELECT statement is called a nested subquery.

Subqueries are divided into correlated subqueries and uncorrelated subqueries. A correlated subquery is a subquery that depends on the variables of the outer query. This query is usually run for multiple times. An uncorrelated subquery is a subquery that does not depend on the variables of the outer query. This subquery is generally calculated only once. For uncorrelated subqueries and some correlated subqueries, you can rewrite the statements to eliminate subqueries. This achieves the unnesting of the nested subqueries.

Syntax

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 ]

Parameters

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

If the upper-level query references the relevant columns in the subquery, the subquery is run. The upper-level query can be a SELECT, UPDATE, or DELETE statement. You can use subqueries in each statement in the following ways:

• Define the rowset that is to be inserted into the table in the INSERT or CREATE TABLE statement.

• In the CREATE VIEW or CREATE MATERIALIZED VIEW statement, define the rowset that is to be contained in the view or the materialized view.

• In the UPDATE statement, define one or more values to be assigned to the existing rows.

• In the WHERE clause, HAVING clause, or START WITH clause, provide condition values.

• Define the table that contains the query operation.

Unnesting of nested subqueries

Unnesting of nested subqueries is a database optimization strategy. It places some subqueries in the parent query of the outer layer. The essence of this operation is to convert some subqueries into equivalent multi-table join operations. One benefit of this strategy is that it can effectively use the access path, join method, and join order to minimize the number of query layers.

In the following cases, the nested subqueries in the database are unnested:

• Uncorrelated IN subqueries.

• The correlated subqueries in IN and EXISTS do not contain aggregate functions or the GROUP BY clauses.

You can use a UNNEST hint to control whether to unnest a nested subquery.

Examples

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

CREATE TABLE table_a(PK INT, name VARCHAR(25));
INSERT INTO table_a VALUES(1,'Fox');
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,'Fox');
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,'Scotch whisky');

The subquery has no dependency relationship. Execute the following statement:

SELECT * FROM TABLE_A T1 WHERE T1.PK IN (SELECT T2.PK FROM TABLE_B T2);

The following query result is returned:

+------+-----------+
| PK   | NAME      |
+------+-----------+
|    1 | Fox    |
|    2 | Police      |
|    3 | Taxi      |
|    6 | Washington    |
|    7 | Dell      |
+------+-----------+

The subquery has the dependency relationship. The outer query variable T1.PK is used in the subquery. Execute the following statement:

SELECT * FROM TABLE_A T1 WHERE T1.PK IN (SELECT T2.PK FROM TABLE_B T2 WHERE T2.PK = T1.PK);

The following query result is returned:

+------+-----------+
| PK   | NAME      |
+------+-----------+
|    1 | Fox    |
|    2 | Police      |
|    3 | Taxi      |
|    6 | Washington    |
|    7 | Dell      |
+------+-----------+

The subquery that has the dependency relationship is unnested and rewritten as a join. Execute the following statement:

EXPLAIN SELECT * FROM TABLE_A T1 WHERE T1.PK IN (SELECT T2.NAME FROM TABLE_B T2 WHERE T2.NAME = T1.NAME);

The following query result is returned:

+------------------------------------+
| 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)
+------------------------------------+