Subquery Operators
A subquery is a query nested within another one. TiDB Cloud Lake supports the following subquery types:
Scalar Subquery
A scalar subquery selects only one column or expression and returns only one row at most. An SQL query can have scalar sub queries in any places where a column or expression is expected.
- If a scalar subquery returns 0 rows, TiDB Cloud Lake will use NULL as the subquery output.
- If a scalar subquery returns more than one row, TiDB Cloud Lake will throw an error.
Examples
CREATE TABLE t1 (a int);
CREATE TABLE t2 (a int);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (3);
INSERT INTO t2 VALUES (3);
INSERT INTO t2 VALUES (4);
INSERT INTO t2 VALUES (5);
SELECT *
FROM t1
WHERE t1.a < (SELECT Min(t2.a)
FROM t2);
--
+--------+
| a |
+--------+
| 1 |
| 2 |
+--------+
EXISTS / NOT EXISTS
An EXISTS subquery is a boolean expression that can appear in a WHERE clause:
- An EXISTS expression evaluates to TRUE if any rows are produced by the subquery.
- A NOT EXISTS expression evaluates to TRUE if no rows are produced by the subquery.
Syntax
[ NOT ] EXISTS ( <query> )
Examples
SELECT number FROM numbers(10) WHERE number>5 AND exists(SELECT number FROM numbers(5) WHERE number>4);
SELECT number FROM numbers(5) WHERE number>4 no rows are produced, exists(SELECT number FROM numbers(5) WHERE number>4) is FALSE.
SELECT number FROM numbers(10) WHERE number>5 and exists(SELECT number FROM numbers(5) WHERE number>3);
+--------+
| number |
+--------+
| 6 |
| 7 |
| 8 |
| 9 |
+--------+
EXISTS(SELECT NUMBER FROM NUMBERS(5) WHERE NUMBER>3) is TRUE.
SELECT number FROM numbers(10) WHERE number>5 AND not exists(SELECT number FROM numbers(5) WHERE number>4);
+--------+
| number |
+--------+
| 6 |
| 7 |
| 8 |
| 9 |
+--------+
not exists(SELECT number FROM numbers(5) WHERE number>4) is TRUE.
IN / NOT IN
By using IN or NOT IN, you can check whether an expression matches any value in a list returned by a subquery.
- When you use IN or NOT IN, the subquery must return a single column of values.
Syntax
[ NOT ] IN ( <query> )
Examples
CREATE TABLE t1 (a int);
CREATE TABLE t2 (a int);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (3);
INSERT INTO t2 VALUES (3);
INSERT INTO t2 VALUES (4);
INSERT INTO t2 VALUES (5);
-- IN example
SELECT *
FROM t1
WHERE t1.a IN (SELECT *
FROM t2);
--
+--------+
| a |
+--------+
| 3 |
+--------+
-- NOT IN example
SELECT *
FROM t1
WHERE t1.a NOT IN (SELECT *
FROM t2);
--
+--------+
| a |
+--------+
| 1 |
| 2 |
+--------+
ANY (SOME)
You can use ANY (or SOME) to check whether a comparison is true for any of the values returned by a subquery.
- The keyword ANY (or SOME) must follow one of the Comparison Operators.
- If the subquery doesn't return any values, the comparison evaluates to false.
- SOME work the same way as ANY.
Syntax
-- ANY
comparison_operator ANY ( <query> )
-- SOME
comparison_operator SOME ( <query> )
Examples
CREATE TABLE t1 (a int);
CREATE TABLE t2 (a int);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (3);
INSERT INTO t2 VALUES (3);
INSERT INTO t2 VALUES (4);
INSERT INTO t2 VALUES (5);
SELECT *
FROM t1
WHERE t1.a < ANY (SELECT *
FROM t2);
--
+--------+
| a |
+--------+
| 1 |
| 2 |
| 3 |
+--------+
ALL
You can use ALL to check whether a comparison is true for all of the values returned by a subquery.
- The keyword ALL must follow one of the Comparison Operators.
- If the subquery doesn't return any values, the comparison evaluates to true.
Syntax
comparison_operator ALL ( <query> )
Examples
CREATE TABLE t1 (a int);
CREATE TABLE t2 (a int);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (3);
INSERT INTO t2 VALUES (3);
INSERT INTO t2 VALUES (4);
INSERT INTO t2 VALUES (5);
SELECT *
FROM t1
WHERE t1.a < ALL (SELECT *
FROM t2);
--
+--------+
| a |
+--------+
| 1 |
| 2 |
+--------+