Subquery

A subquery is a query nested within another SELECT statement and can return one row, multiple rows, or nothing. A SELECT statement can be nested with one or more other SELECT statements. A subquery in the FROM clause of a SELECT statement is also called an inner join view. You can nest any number of subqueries in a nested view. A subquery in the WHERE clause of a SELECT statement is also called a nested subquery.

Subqueries can be divided into dependent subqueries and independent subqueries. A dependent subquery means that the execution of this subquery depends on variables of outer queries. Dependent queries are usually executed multiple times. An independent subquery means that the execution of this subquery does not depend on variables of outer queries. Independent subqueries are typically executed only once. You can rewrite independent subqueries and some of the dependent subqueries to unnest nested subqueries.

Syntax

The general syntax for subqueries 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 in the syntax.

If a column in a subquery has the same name as a column in the outer query, the table name or an alias must be prefixed to the duplicate column name in the outer query.

When the outer query references a column in a subquery, the subquery is executed. The outer query can be the SELECT, UPDATE, or DELETE statement. The rules for referencing subqueries in different statements are as follows:

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

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

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

• Specify conditions in the WHERE, HAVING, or START WITH clause.

• Define a table that involves query operations.

Unnesting of nested subqueries

Subquery unnesting is a database optimization strategy. It places some subqueries in the outer parent query to convert some subqueries into equivalent multi-table join operations. This strategy can reduce the query layers as possible by using the access path, join method, and join order.

OceanBase Database will unnest the following types of subqueries:

• Independent IN subqueries

• Dependent subqueries in IN and EXISTS that do not contain aggregate functions or the GROUP BY clause

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

Examples

Create two tables respectively named table_a and table_b, and insert data into them.

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');

• Independent subquery

  obclient> SELECT * FROM table_a t1 WHERE t1.PK IN (SELECT t2.PK FROM table_b t2);
  +------+-----------+
  | PK   | NAME      |
  +------+-----------+
  |    1 | Fox       |
  |    2 | Police    |
  |    3 | Taxi      |
  |    6 | Washington|
  |    7 | Dell      |
  +------+-----------+
  5 rows in set
  

• Dependent subquery that uses 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 | Fox       |
  |    2 | Police    |
  |    3 | Taxi      |
  |    6 | Washington|
  |    7 | Dell      |
  +------+-----------+
  5 rows in set
  

• Dependent subquery that is unnested and rewritten as a join operation

  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