The database also supports subquery expressions. The subquery return values listed below are TRUE/FALSE.
The data in the original table t1 is as follows:
select * from t1;a1----132
EXISTS/NOT EXISTS
The parameter of EXISTS is a SELECT statement, or a subquery. The system performs operations on the subquery to determine if it returns any rows. If it returns at least one row, the result of EXISTS is TRUE; if the subquery returns no rows, the result of EXISTS is FALSE.
This subquery will generally only run long enough to determine whether at least one row is returned, not all the way to completion. Therefore, the content of the results returned by the subquery is usually not what we are concerned with, so the optimized writing style is:
...WHERE EXISTS(SELECT 1 FROM ...) .Sample code | Result |
select a1 from t1 where EXISTS(select a1 from t1 where a1>2); | a1 ---- 2 1 3 |
select a1 from t1 where EXISTS(select a1 from t1 where a1>3); | a1 ---- |
select a1 from t1 where EXISTS(select a1 from t1 where a1>1) and a1>2; | a1 ---- 3 |
IN/NOT IN
expression IN (subquery), where the right side is a subquery enclosed in parentheses, and it must return only one field. The left expression performs one calculation and comparison per row of the subquery results. If any equal row is found in the subquery, the result of IN is TRUE. If no equal rows are found, the result is FALSE (including when the subquery returns no rows).
NULL values in expressions or subquery rows follow SQL rules when dealing with Boolean values and NULL combinations. If corresponding fields in both rows are equal and non-null, the rows are equal; if any corresponding fields are unequal but non-null, the rows are unequal; otherwise, the result is unknown (NULL). If each result row is either unequal or NULL, and there is at least one NULL, then the result of IN is NULL.
Pay special attention to subqueries in NOT IN, if there are NULL values, it directly returns FALSE.
Sample code | Result |
select * from t1 where a1 in (select a1 from t1 where a1 > 2); | a1 ---- 3 (1 row) |
select * from t1 where a1 in (4,5,6); | a1 ---- (0 rows) |
select * from t1 where a1 in (2,5,6); | a1 ---- 2 (1 row) |
ANY/SOME
expression operator SOME/ANY (subquery), where the right side is a subquery and must return only one field value. The left expression uses the operator to perform one calculation and comparison per row of the subquery result, and the result must be a Boolean value. If at least one true value is obtained, the result of ANY is TRUE. If all false values are obtained, the result is FALSE (including when the subquery returns no rows). SOME is a synonym of ANY. IN and ANY can be used as equivalent substitutions.
Similar to EXISTS, this comparison with the subquery results will generally only run long enough to determine if it is TRUE, not all the way to completion.
Sample code | Result |
select * from t1 where a1 < any(select a1 from t1 where a1 <3); | a1 ---- 1 (1 row) |
select * from t1 where a1 < some(select a1 from t1 where a1 <4); | a1 ---- 1 2 (2 rows) |
select * from t1 where a1 < any(select a1 from t1 where a1 <1); | a1 ---- (0 rows) |
ALL
expression operator ALL (subquery), where the right side is a subquery enclosed in parentheses and must return only one field. The left expression uses the operator to perform one calculation and comparison per row of the subquery result, and the result must be a Boolean value. If all true values are obtained, the result of ALL is TRUE (including when the subquery returns no rows). If at least one false value is obtained, the result is FALSE.
NOT IN is equivalent to ALL.
Similar to EXISTS, this comparison with the subquery results will generally only run long enough to determine if it is FALSE, not all the way to completion.
If the subquery returns NULL, it directly returns TRUE.
Sample code | Result |
select * from t1 where a1 < all(select a1 from t1 where a1 <1); | a1 ---- 1 3 2 (3 rows) |
select * from t1 where a1 < all(select a1 from t1 where a1 >2); | a1 ---- 2 1 (2 rows) |