All Products
Search
Document Center

Overview

Last Updated: Dec 31, 2019

An SQL query refers to the method used to obtain data from the database. An SQL query can be used with conditional clauses such as WHERE and ordering clauses such as ORDER BY to obtain query results. A subquery is a query that is nested within an external query. The external query is also called the parent query or the outer query. The result of the subquery is passed back as input to the parent query or outer query. The parent query uses this value in the computation to determine the final output. SQL allows multiple levels of nested queries, which means one subquery can also have other subqueries nested. At the same time, subqueries can appear in various clauses in SQL statements, such as SELECT statements, FROM statements, and WHERE statements.

Subqueries

In the database, subqueries can be divided into dependent subqueries and independent subqueries. A dependent subquery is a subquery whose execution depends on the variable of an external query. Dependent subqueries are usually calculated multiple times. An independent subquery is a subquery whose execution does not depend on the variables of an external query. Such subqueries are calculated once. The following figure shows an independent query and a dependent query.

  1. OceanBase (root@test)> create table t1(a int primary key, b int, c int);
  2. Query OK, 0 rows affected (0.70 sec)
  3. OceanBase (root@test)> create table t2(a int primary key, b int, c int);
  4. Query OK, 0 rows affected (0.92 sec)
  5. -- An independent query
  6. OceanBase (root@test)> select * from t1 where t1.a in (select t2.a from t2);
  7. Empty set (0.22 sec)
  8. -- A dependent query. The outer query variable t1.b is used in the subquery
  9. OceanBase (root@test)> select * from t1 where t1.a in (select t2.a from t2 where t2.b = t1.b);
  10. Empty set (0.05 sec)

Subquery unnesting

Subquery unnesting is a database optimization strategy. It merges the subquery into the body of the outer query by converting some subqueries into equivalent multi-table JOIN operations. The benefit of this strategy is that the optimizer can choose better access paths, join methods, and join orders so that the query hierarchy is reduced as much as possible. The following figure shows an example of a subquery unnesting, where the subquery is rewritten into a JOIN statement.

  1. OceanBase (root@test)> create table t1(a int primary key, b int, c int);
  2. Query OK, 0 rows affected (0.70 sec)
  3. OceanBase (root@test)> create table t2(a int primary key, b int, c int);
  4. Query OK, 0 rows affected (0.92 sec)
  5. --- Dependent subqueries are unnested and rewritten into joins
  6. OceanBase (root@test)> explain select * from t1 where t1.a in (select t2.b from t2 where t2.c = t1.c);
  7. | =======================================
  8. |ID|OPERATOR |NAME|EST. ROWS|COST|
  9. ---------------------------------------
  10. |0 |HASH SEMI JOIN| |1 |2924|
  11. |1 | TABLE SCAN |t1 |1000 |455 |
  12. |2 | TABLE SCAN |t2 |1000 |455 |
  13. =======================================
  14. Outputs & filters:
  15. -------------------------------------
  16. 0 - output([t1.a], [t1.b], [t1.c]), filter(nil),
  17. equal_conds([t1.a = t2.b], [t2.c = t1.c]), other_conds(nil)
  18. 1 - output([t1.c], [t1.a], [t1.b]), filter(nil),
  19. access([t1.c], [t1.a], [t1.b]), partitions(p0)
  20. 2 - output([t2.c], [t2.b]), filter(nil),
  21. access([t2.c], [t2.b]), partitions(p0)