Nested Queries

April 3, 2025About 16 min

Nested Queries

1. Overview

Nested queries, also known as subqueries, refer to query statements that contain one or more other query statements within them. A nested query consists of an inner query and an outer query.

2. Classification of Nested Queries

Nested queries can be classified based on two criteria: whether they reference the outer query and the dimensionality of the result set.

Classification by Reference to Outer Query:

Classification Basis	Nested Query Type	Description	IoTDB Support
Whether to Reference Outer Query	Non-Correlated Subquery	The execution of the inner query is independent of the outer query. The inner query executes only once, and its result is used as a condition for the outer query.	Supported
Whether to Reference Outer Query	Correlated Subquery	The inner query references columns from the outer query's table, requiring the outer query to execute first, followed by the inner query.	Not Supported

Classification by Result Set Dimensionality:

Classification Basis	Subquery Type	Description	Usable Clauses	IoTDB Support
Result Set Dimensionality	Scalar Subquery	Returns a single row and column.	select、where、from、having	Supported
	Column Subquery	Returns N rows and one column.	select、where、from、having	Supported
	Row Subquery	Returns one row and N columns.	-	Not Supported
	Table Subquery	Returns N rows and M columns.	from	Supported

3. Functional Definitions

All inner queries must be enclosed in parentheses (subquery).
Non-correlated subqueries cannot reference columns from the outer query. Attempting to do so will result in an error:
Msg: org.apache.iotdb.jdbc.IoTDBSQLException： 701: Given correlated subquery is not supported

3.1 Non-Correlated Scalar Subqueries

A scalar subquery returns a single scalar value and can be used to replace an operand in an expression.

Syntax

primaryExpression
    : literalExpression                                                   #Literal
    | dateExpression #dateTimeExpression                                  #dateTimeExpression
    | '(' expression (',' expression)+ ')'                                #rowConstructor
    | ROW '(' expression (',' expression)* ')'                            #rowConstructor
    | qualifiedName '(' (label=identifier '.')? ASTERISK ')'              #functionCall
    | qualifiedName '(' (setQuantifier? expression (',' expression)*)?')' #functionCall
    | '(' query ')'                                                       #subqueryExpression

Notes

Scalar subqueries can be used as operands in any expression unless the input parameters are explicitly defined as constants.

Examples where scalar subqueries cannot be used as parameters:

The first and third parameters of date_bin(interval,source,origin).
The first and third parameters of date_bin_gapfill(interval,source,origin).
- interval ：Time interval
- origin：Starting timestamp
Fill parameters
- fill previous
- fill linear
- fill constant

3.2 Non-Correlated Column Subqueries

3.2.1 Non-Correlated InPredicate

An InPredicate is a predicate that returns a column of boolean values.

Syntax

predicate[ParserRuleContext value]
    : comparisonOperator right=valueExpression                           #comparison
    | comparisonOperator comparisonQuantifier '(' query ')'              #quantifiedComparison
    | NOT? BETWEEN lower=valueExpression AND upper=valueExpression       #between
    | NOT? IN '(' expression (',' expression)* ')'                       #inList
    | NOT? IN '(' query ')'                                              #inSubquery
    | NOT? LIKE pattern=valueExpression (ESCAPE escape=valueExpression)? #like
    | IS NOT? NULL                                                       #nullPredicate
    | IS NOT? DISTINCT FROM right=valueExpression                        #distinctFrom
    ;

Notes

Usage: X [NOT] IN (subquery)

X is an expression.
NOT is optional (negation).
subquery returns a result set Resultwith one column and multiple rows.
For WHERE X IN (subquery), each row where X is in Result will be retained in the SELECT output.

3.2.2 Non-Correlated Quantified Comparison

A quantified comparison allows comparing a value to a set of values, typically consisting of:

Comparison operators:<， >， =， <=， >=， !=
Quantifiers:
1. ALL: All elements must satisfy the condition.
2. ANY or SOME: At least one element must satisfy the condition (ANY and SOME are equivalent).
Subquery: Returns a set of values to compare with the main query's value.

Syntax

predicate[ParserRuleContext value]
    : comparisonOperator right=valueExpression                           #comparison
    | comparisonOperator comparisonQuantifier '(' query ')'              #quantifiedComparison
    | NOT? BETWEEN lower=valueExpression AND upper=valueExpression       #between
    | NOT? IN '(' expression (',' expression)* ')'                       #inList
    | NOT? IN '(' query ')'                                              #inSubquery
    | NOT? LIKE pattern=valueExpression (ESCAPE escape=valueExpression)? #like
    | IS NOT? NULL                                                       #nullPredicate
    | IS NOT? DISTINCT FROM right=valueExpression                        #distinctFrom
    ;
comparisonQuantifier
    : ALL | SOME | ANY
    ;

Notes

Usage: expression operator ALL/ANY/SOME (subquery)

ALL: The expression in the main query must satisfy the condition with every value returned by the subquery.
ANY/SOME: The expression in the main query must satisfy the condition with at least one value returned by the subquery.

From the table1 table, group by device_id, calculate the record count for each device ID, and find all device IDs and their corresponding record counts where the result of adding 25 to the grouped record count appears in the s1 field values of records with device ID 'd01' in the table3 table.

SQL:

IoTDB> SELECT device_id, count(*) from table1 
             group by device_id 
             having count(*) + 25 
             in (SELECT cast(s1 as INT64) from table3 where device_id = 'd01');

Result:

+---------+-----+
|device_id|_col1|
+---------+-----+
|      d01|    5|
|      d03|    5|
|      d05|    5|
|      d07|    5|
|      d09|    5|
|      d11|    5|
|      d13|    5|
|      d15|    5|
+---------+-----+
Total line number = 8

Select Clause

From table1, select records where device_id = 'd01' and check whether their s1 field values exist in the s1 field values of records with the same device ID (d01) in table3.

SQL:

IoTDB> SELECT 
          s1 in (SELECT s1 from table3 WHERE device_id = 'd01') 
          from table1 where device_id = 'd01';

Result:

+-----+
|_col0|
+-----+
| true|
| true|
|false|
|false|
|false|
+-----+
Total line number = 5

Special Cases

When using InPredicate in the SELECT clause (SELECT x [NOT] IN (subquery) FROM table), the result rules are summarized as follows:

If the current row's x is null, the result is null
If the current row's x is not null:
- Without NOT:
  - If x exists in the subquery result set, the current row result is True
  - If x does not exist in the subquery result set:
    - If the subquery result set contains null, the current row result is null; otherwise, it's False
- With NOT:
  - If x exists in the subquery result set, the current row result is False
  - If x does not exist in the subquery result set:
    - If the subquery result set contains null, the current row result is null; otherwise, it's True

Example 1: When X result set contains null

IoTDB> select s1 from table3;
+----+
|  s1|
+----+
|  30|
|null|
|  30|
|  40|
+----+
Total line number = 4

IoTDB> select s1 from table3 where s1 in (select s1 from table3);
+--+
|s1|
+--+
|30|
|30|
|40|
+--+
Total line number = 3

Example 2: When used in the SELECT clause, the corresponding row's result is null

IoTDB> select device_id, s1 in (select s1 from table1 where device_id = 'd01'), s1 from table3 ;
+---------+-----+----+
|device_id|_col1|  s1|
+---------+-----+----+
|   d_null| true|  30|
|      d01| true|  30|
|      d01| true|  40|
|   d_null| null|null|
+---------+-----+----+

Example 3: When the subquery result set contains null values

IoTDB> select s1 from table1 where device_id = 'd02'
+----+
|  s1|
+----+
|  36|
|  40|
|null|
+----+
Total line number = 3

IoTDB> select s1 from table3;
+----+
|  s1|
+----+
|  30|
|null|
|  30|
|  40|
+----+
Total line number = 4

Example 4: Use in the where clause, that is, where s1 in (subquery). The result set only contains one row of 40.

IoTDB> select s1 from table1 where device_id = 'd02' and s1 in (select s1 from table3);
+--+
|s1|
+--+
|40|
+--+
Total line number = 1

Example 5: Use it in the select clause. The result set of s1 is (36, 40, null), and the result set of subquery is (30, 40, null). Since 36 is not equal to the two non-null results 30 and 40, and the result set of subquery contains null, the corresponding result is null.

IoTDB> SELECT 
>     s1 in (SELECT s1 from table3) from table1 
>         where device_id = 'd02'
+-----+
|_col0|
+-----+
| null|
| true|
| null|
+-----+
Total line number = 3

4.3.2 Non-Correlated Quantified Comparison

WHERE Clause

Find the records with the equipment number d01 from the table1, and the value of the s1 field must be greater than all the s1 field values of the same equipment number in the table3.

SQL:

IoTDB> SELECT s1 FROM table1 
          WHERE device_id = 'd01' and 
          s1 > all (SELECT s1 FROM table3 WHERE device_id = 'd01');

Result:

+--+
|s1|
+--+
|50|
|60|
|70|
+--+
Total line number = 3

ANY/SOME

Find the records with the equipment number d01 from the table1, and the value of the s1 field must be greater than the s1 field value of any record with the same equipment number in the table3.

SQL:

IoTDB> SELECT s1 FROM table1 
          WHERE device_id = 'd01' and 
          s1 > any (SELECT s1 FROM table1 WHERE device_id = 'd01');

Result:

+--+
|s1|
+--+
|40|
|50|
|60|
|70|
+--+
Total line number = 4

HAVING Clause

Group the records in table1 by device_id (equipment number), calculate the number of records for each equipment number. Then, find the equipment numbers and their corresponding record counts such that the sum of the group record count and 35 is greater than or equal to all the s1 field values (converted to the integer type) of the equipment number d01 in table3.

SQL:

IoTDB> SELECT device_id, count(*) from table1 
          group by device_id 
          having count(*) + 35 >= 
          all(SELECT cast(s1 as INT64) from table3 where device_id = 'd01');

Result:

+---------+-----+
|device_id|_col1|
+---------+-----+
|      d01|    5|
|      d03|    5|
|      d05|    5|
|      d07|    5|
|      d09|    5|
|      d11|    5|
|      d13|    5|
|      d15|    5|
+---------+-----+
Total line number = 8

ANY/SOME

Group the records in table1 by device_id (equipment number), calculate the number of records for each equipment number. Then, find the equipment numbers and their corresponding record counts such that the sum of the group record count and 35 is greater than or equal to any one of the s1 field values (converted to the integer type) of the equipment number d01 in table3.

SQL:

IoTDB> SELECT device_id, count(*) 
          from table1 group by device_id 
          having count(*) + 25 >= 
          any(SELECT cast(s1 as INT64) from table3 where device_id = 'd01');

Result:

+---------+-----+
|device_id|_col1|
+---------+-----+
|      d01|    5|
|      d03|    5|
|      d05|    5|
|      d07|    5|
|      d09|    5|
|      d11|    5|
|      d13|    5|
|      d15|    5|
+---------+-----+
Total line number = 8

SELECT Clause

Select the records with the equipment number d01 from table1, and the value of the s1 field must be greater than all the s1 field values of the equipment number d01 in table3.

SQL:

IoTDB> SELECT s1 > 
         all(SELECT (s1) from table3 WHERE device_id = 'd01') 
         from table1 where device_id = 'd01';

Result:

+-----+
|_col0|
+-----+
|false|
|false|
| true|
| true|
| true|
+-----+
Total line number = 5

ANY/SOME

Select the records with the equipment number d01 from table1, and the value of the s1 field must be greater than any one of the s1 field values of the equipment number d01 in table3.

SQL:

IoTDB> SELECT s1 > 
          any(SELECT (s1) from table3 WHERE device_id = 'd01') 
          from table1 where device_id = 'd01';

Result:

+-----+
|_col0|
+-----+
|false|
| true|
| true|
| true|
| true|
+-----+
Total line number = 5

Special Cases

ALL requires all comparisons to be True for the result to be True.

ANY/SOME requires any comparison to be True for the result to be True.

Example 1: ALL

IoTDB> select s1 from table1 where device_id = 'd01'
+--+
|s1|
+--+
|30|
|40|
|50|
|60|
|70|
+--+
Total line number = 5

IoTDB> select s1 from table3;
+----+
|  s1|
+----+
|  30|
|null|
|  30|
|  40|
+----+

IoTDB> select (s1 > all(select s1 from table3)) from table1 where device_id = 'd01';
+-----+
|_col0|
+-----+
|false|
|false|
| null|
| null|
| null|
+-----+

Note:

For the two rows of 30 and 40 in table1s1, since the non-null result set (30, 40) of table3 s1 makes 30 > 40 / 40 > 40 evaluate to False, that is, short-circuit evaluation occurs, and the result is false.
For the three values of 50, 60, and 70, since ALL requires all comparison results to be True for the final result to be True, the comparisons of 50, 60, and 70 with null are all null, and the result is null.

Example 2: ANY/SOME

IoTDB> SELECT s1 <= 
         any(SELECT (s1) from table3), s1 <= any(SELECT (s1) from table3 where s1 is not NULL)
         from table1 where device_id = 'd01'
+-----+-----+
|_col0|_col1|
+-----+-----+
| true| true|
| true| true|
| null|false|
| null|false|
| null|false|
+-----+-----+

Note:

For 30 and 40 in table1, because of the non-null result set (30, 40) of table3 s1, the comparison results are true (valid).
For 50, 60, and 70, since ANY requires at least one comparison result to be true for the overall result to be true, and the comparison results with null are null, these results are null.
In the second query, because we have excluded null values, for 50, 60, and 70, there are no larger non-null values in table3, so the comparison results are false.

Nested Queries

Nested Queries

1. Overview

2. Classification of Nested Queries

3. Functional Definitions

3.1 Non-Correlated Scalar Subqueries

3.2 Non-Correlated Column Subqueries

3.2.1 Non-Correlated InPredicate

3.2.2 Non-Correlated Quantified Comparison

4. Usage Examples

4.1 Example Data

4.2 Non-Correlated Scalar Subquery Examples

4.3 Non-Correlated Column Subquery Examples

4.3.1 Non-Correlated InPredicate

4.3.2 Non-Correlated Quantified Comparison