A subquery is a query nested within an outer query. The outer query is typically referred to as the parent query or 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 its calculations to determine the final output.
The SQL language supports multi-level nested queries, meaning a subquery can also contain other subqueries. Subqueries can appear in various clauses of an SQL statement, such as the SELECT, FROM, and WHERE clauses.
Subqueries
In a database, subqueries can be divided into dependent subqueries and independent subqueries. A dependent subquery is one whose execution depends on variables from the outer query, so such subqueries are typically evaluated multiple times. An independent subquery is one whose execution does not depend on variables from the outer query; these subqueries generally only need to be evaluated once. The following examples illustrate independent and dependent subqueries respectively.
Subqueries with no dependencies:
obclient> SELECT * FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2);A subquery with a dependency, where the outer query variable
t1.bis used in the subquery:obclient> SELECT * FROM t1 WHERE t1.a IN (SELECT t2.a FROM T2 WHERE t2.b = t1.b);
Scalar subquery
A subquery that returns a single element is called a scalar subquery. The query result can be used as a scalar operand and has the basic characteristics of a scalar operand, such as data type, length, and whether it can be NULL. For example, the result of the following query can be used as a scalar operand:
obclient> CREATE TABLE t1 (c1 INTEGER, c2 VARCHAR(20) NOT NULL);
obclient> INSERT INTO t1 VALUES (1, 'OceanBase');
obclient> SELECT (SELECT c2 FROM t1);
+---------------------+
| (SELECT c2 FROM t1) |
+---------------------+
| OceanBase |
+---------------------+
1 row in set
The preceding query returns a value of type VARCHAR with a length of 20, which is OceanBase. The character set and collation follow the tenant's default values. The nullability of the selected values in the subquery is not replicated. Although column c2 has a NOT NULL constraint, if the result of the subquery is empty, the result will still be NULL.
Although they return a single element, scalar subqueries cannot be used in all statements. If a statement only allows literals, you cannot use a scalar subquery as a substitute.
The following examples can help you better understand scalar subqueries.
This query returns 2 because table t2 contains a column named c1, which has the same name as the column c1 in table t1.
obclient> CREATE TABLE t1 (c1 INT);
obclient> INSERT INTO t1 VALUES (1);
obclient> CREATE TABLE t2 (c1 INT);
obclient> INSERT INTO t2 VALUES (2);
obclient> SELECT (SELECT c1 FROM t2) FROM t1;
+---------------------+
| (SELECT s1 FROM t2) |
+---------------------+
| 2 |
+---------------------+
1 row in set
In addition, a scalar subquery can be part of an expression. For example:
obclient> CREATE TABLE t1 (c1 INTEGER, c2 VARCHAR(20) NOT NULL);
obclient> INSERT INTO t1 VALUES (1, 'OceanBase');
obclient> SELECT HEX((SELECT c2 FROM t1 WHERE c1=1)) FROM t1;
+-------------------------------------+
| HEX((SELECT c2 FROM t1 WHERE c1=1)) |
+-------------------------------------+
| 4F6365616E42617365 |
+-------------------------------------+
1 row in set
Comparison of subqueries
The common syntax for subqueries is as follows:
operand operator {ANY | SOME | ALL} (subquery)
operand LIKE (subquery)
operand {IN | NOT IN | EXISTS | NOT EXISTS} (subquery)
Here, operand is the operand used to compare with the result of the subquery. operator can be one of the following operators:
Equal to (
=or<=>)Greater than (
>)Less than (
<)Greater than or equal to (
>=)Less than or equal to (
<=)Not equal to (
!=or<>)LIKEis used for character or string comparison.
For example, the following subquery is used to find the values in column c1 of table t1 that are equal to the maximum value in column c2 of table t2. This query cannot be rewritten as a join between t1 and t2.
obclient> CREATE TABLE t1 (c1 INT);
obclient> INSERT INTO t1 VALUES (1);
obclient> CREATE TABLE t2 (c2 INT);
obclient> INSERT INTO t2 VALUES (2);
obclient> SELECT * FROM t1 WHERE c1 = (SELECT MAX(c2) FROM t2);
Notice
For comparison with a scalar, the subquery must return a scalar. For comparison with a row constructor, the subquery must be a row subquery and return a row with the same attributes as the row constructor.
Clearly, only the results of scalar subqueries can be used for direct comparison. If a subquery returns a column of values, the set must be processed. OceanBase AI Database provides the following methods to aggregate a set into a scalar value:
Aggregate functions: Aggregate all values in a set into a single value, which is then compared with the operand.
ANY | SOME: Compares all values in the set with the operand. If any row meets the comparison condition,TRUEis returned.SOMEis an alias forANY.ALL: Compares all values in the set with the operand. If all rows meet the comparison condition,TRUEis returned.IN: ReturnsTRUEif the operand belongs to the set.INis equivalent to= ANY.NOT IN: ReturnsTRUEif the operand does not belong to the set.NOT INis equivalent to<> ALL.EXIST: ReturnsTRUEif the subquery returns any rows.NOT EXIST: ReturnsTRUEif the subquery returns no rows.
Row subquery
A scalar subquery or a column subquery returns a scalar or a single column of values, whereas a row subquery returns a single row, which can contain multiple columns of values.
For example, the following subquery returns only a single row:
obclient> CREATE TABLE t1(id INTEGER PRIMARY KEY,c1 INTEGER,c2 INTEGER);
obclient> CREATE TABLE t2(id INTEGER PRIMARY KEY,c3 INTEGER,c4 INTEGER);
obclient> SELECT * FROM t1
WHERE ROW(c1,c2) = (SELECT c3, c4 FROM t2 WHERE id = 10);
If the values of c3 and c4 are equal to any pair of values c1 and c2 in t1, then all such pairs c1, c2 are returned. If no such pairs exist, an empty set is returned. Furthermore, if a WHERE condition is not specified in the subquery, it may return multiple rows, causing an error in the main query. A row subquery can only return a single row.
The expression ROW(c1,c2) can be abbreviated as (c1,c2). The ROW() function is called the row constructor. The row constructor and the subquery must return the same number of values. The row constructor is used to compare with subqueries that return two or more columns. It cannot be used together with column subqueries that return a single column. For example, the following query will return a syntax error:
obclient> SELECT * FROM t1 WHERE ROW(1) = (SELECT column1 FROM t2);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near ') = (SELECT c1 FROM t2)' at line 1
Row constructors are expanded in the optimizer. For example, the following two SQL statements are equivalent:
obclient> SELECT * FROM t1 WHERE (c1,c2) = (1,1);
obclient> SELECT * FROM t1 WHERE c1 = 1 AND c2 = 1;
Correlated subqueries
A subquery is called a correlated subquery if it references columns from the outer query. For example, in the following form, the WHERE condition of the subquery references column c2 of t1:
obclient> SELECT * FROM t1 WHERE c1 IN (SELECT c3 FROM t2 WHERE t2.c4 = t1.c2);
For such statements, the query is evaluated from inner to outer. For example, in the following statement, x.c2 must be a column from t2, not from t1. This is because the statement SELECT c1 FROM t2 AS x first renames t2, while SELECT c1 FROM t1 is the outer query.
obclient> SELECT c1 FROM t1 AS x
WHERE x.c1 = (SELECT c1 FROM t2 AS x
WHERE x.c1 = (SELECT c1 FROM t3
WHERE x.c2 = t3.c1));
For subqueries in the HAVING or ORDER BY clause, OceanBase AI Database also searches for column names in the external SELECT list.
If an aggregate function in a correlated subquery contains an external reference, the function must contain only that external reference and cannot be nested within another function or expression.
Derived table
A derived table is an expression that generates a table within the scope of the FROM clause, for example, a query in the FROM clause of a SELECT statement can be a derived table:
SELECT ... FROM (subquery) [AS] table_name ...
As a derived table in the FROM clause, [AS] table_name is no longer an optional alias; it must be a name specified for the result set of the subquery. Every column in the derived table must have its own unique name.
Derived tables are often used for step-by-step calculations. For example, the following query calculates the mean of each group after grouping:
obclient> SELECT AVG(sum_c1)
FROM (SELECT SUM(c1) AS sum_cumn1
FROM t1 GROUP BY c1) AS t1;
Derived tables can return scalars, columns, rows, or tables.
Derived tables have the following limitations:
A derived table cannot be a correlated subquery.
A derived table cannot contain references to other tables in the same
SELECTstatement.A derived table cannot contain external references.
Lateral derived table
A derived table is a table that can reference previously defined tables or derived tables in the FROM list within the FROM clause.
Lateral derived tables can provide greater flexibility and efficiency when using subqueries, especially when the subquery depends on columns from the outer query.
Example:
SELECT *
FROM A, LATERAL (SELECT * FROM B WHERE B.col1 = A.col1) AS derived_table;
For more information about horizontal derived tables, see Lateral derived table.
Subquery Unnesting
Subquery expansion is an optimization strategy in databases. It incorporates some subqueries into the outer parent query, essentially transforming certain subqueries into equivalent multi-table join operations. A clear benefit of this strategy is that it can effectively utilize access paths, join methods, and join order, thereby minimizing the hierarchy of the query statement as much as possible. The following example demonstrates subquery expansion, where a subquery is rewritten as a join statement.
obclient> CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT);
Query OK, 0 rows affected
obclient> CREATE TABLE t2(a INT PRIMARY KEY, b INT, c INT);
Query OK, 0 rows affected
Subqueries without dependencies.
obclient> EXPLAIN SELECT * FROM t1 WHERE t1.a IN (SELECT t2.a FROM t2); | ====================================== |ID|OPERATOR |NAME|EST. ROWS|COST | -------------------------------------- |0 |MERGE JOIN | |100001 |131664| |1 | TABLE SCAN|t1 |100000 |48372 | |2 | TABLE SCAN|t2 |100000 |41911 | ====================================== ...Dependent subqueries are expanded and rewritten as joins.
obclient> EXPLAIN SELECT * FROM t1 WHERE T1.A IN (SELECT T2.B FROM T2 WHERE T2.C = T1.C); | =========================================== |ID|OPERATOR |NAME |EST. ROWS|COST | -------------------------------------------- |0 |MERGE JOIN | |9703 |215436| |1 | TABLE SCAN |t1 |100000 |64066 | |2 | SORT | |10001 |129621| |3 | SUBPLAN SCAN |VIEW1|10001 |111242| |4 | HASH DISTINCT| |10001 |109862| |5 | TABLE SCAN |t2 |100000 |64066 | ============================================ ...