ANY 和 ALL
您通常只对返回一行的子查询使用比较运算符（=、>、< 等）。使用 ANY 和 ALL 运算符，可在返回多个行的子查询中进行比较。
这些子查询采用以下格式：
expression comparison-operator { ANY | ALL } (subquery)
ANY 和 ALL 评估子查询返回的任何或所有值是否与左侧表达式匹配。
等效运算符
可以使用以下运算符代替 ANY 或 ALL：
Example data
以下示例使用以下表和数据：
ANY 子查询
当在子查询中检索的任何值与左侧表达式的值相匹配时，使用 ANY 关键字的子查询返回 true。
示例
表达式中的 ANY 子查询：
=> SELECT c1, c2 FROM t1 WHERE COALESCE((t1.c1 > ANY (SELECT c1 FROM t2)));
c1 | c2
----+-----
2 | fed
2 | def
3 | ihg
3 | ghi
4 | jkl
5 | mno
(6 rows)
ANY 不带有聚合的非相关子查询：
=> SELECT c1 FROM t1 WHERE c1 = ANY (SELECT c1 FROM t2) ORDER BY c1;
c1
----
1
1
2
2
3
3
(6 rows)
ANY 带有聚合的非相关子查询：
=> SELECT c1, c2 FROM t1 WHERE c1 <> ANY (SELECT MAX(c1) FROM t2) ORDER BY c1;
c1 | c2
----+-----
1 | cab
1 | abc
2 | fed
2 | def
4 | jkl
5 | mno
(6 rows)
=> SELECT c1 FROM t1 GROUP BY c1 HAVING c1 <> ANY (SELECT MAX(c1) FROM t2) ORDER BY c1;
c1
----
1
2
4
5
(4 rows)
ANY 带有聚合和 GROUP BY 子句的非相关子查询：
=> SELECT c1, c2 FROM t1 WHERE c1 <> ANY (SELECT MAX(c1) FROM t2 GROUP BY c2) ORDER BY c1;
c1 | c2
----+-----
1 | cab
1 | abc
2 | fed
2 | def
3 | ihg
3 | ghi
4 | jkl
5 | mno
(8 rows)
ANY 带有 GROUP BY 子句的非相关子查询：
=> SELECT c1, c2 FROM t1 WHERE c1 <=> ANY (SELECT c1 FROM t2 GROUP BY c1) ORDER BY c1;
c1 | c2
----+-----
1 | cab
1 | abc
2 | fed
2 | def
3 | ihg
3 | ghi
(6 rows)
ANY 不带有聚合或 GROUP BY 子句的相关子查询：
=> SELECT c1, c2 FROM t1 WHERE c1 >= ANY (SELECT c1 FROM t2 WHERE t2.c2 = t1.c2) ORDER BY c1;
c1 | c2
----+-----
1 | abc
2 | fed
4 | jkl
(3 rows)
ALL 子句
当子查询检索到的所有值都与左侧表达式匹配时，使用 ALL 关键字的子查询返回 true，否则返回 false。
示例
ALL 不带有聚合的非相关子查询：
=> SELECT c1, c2 FROM t1 WHERE c1 >= ALL (SELECT c1 FROM t2) ORDER BY c1;
c1 | c2
----+-----
3 | ihg
3 | ghi
4 | jkl
5 | mno
(4 rows)
ALL 带有聚合的非相关子查询：
=> SELECT c1, c2 FROM t1 WHERE c1 = ALL (SELECT MAX(c1) FROM t2) ORDER BY c1;
c1 | c2
----+-----
3 | ihg
3 | ghi
(2 rows)
=> SELECT c1 FROM t1 GROUP BY c1 HAVING c1 <> ALL (SELECT MAX(c1) FROM t2) ORDER BY c1;
c1
----
1
2
4
5
(4 rows)
ALL 带有聚合和 GROUP BY 子句的非相关子查询：
=> SELECT c1, c2 FROM t1 WHERE c1 <= ALL (SELECT MAX(c1) FROM t2 GROUP BY c2) ORDER BY c1;
c1 | c2
----+-----
1 | cab
1 | abc
(2 rows)
ALL 带有 GROUP BY 子句的非相关子查询：
=> SELECT c1, c2 FROM t1 WHERE c1 <> ALL (SELECT c1 FROM t2 GROUP BY c1) ORDER BY c1;
c1 | c2
----+-----
4 | jkl
5 | mno
(2 rows)
NULL 处理
如果列没有标记为 NOT NULL，则 Vertica 支持多列 <> ALL 子查询。如果任何列包含 NULL 值，Vertica 将返回运行时错误。
如果任何列值为 NULL，则 Vertica 不支持嵌套在另一个表达式中的 = ANY 子查询。