这是本节的多页打印视图。
点击此处打印.
返回本页常规视图.
搜索条件中使用的子查询
子查询需要用作搜索条件才能过滤结果。它们指定了从包含查询的 select-list、查询表达式或子查询自身返回行的条件。此操作评估为 TRUE、FALSE 或 UNKNOWN (NULL)。
语法
search‑condition {
[ { AND | OR | NOT } { predicate | ( search‑condition ) } ]
}[,... ]
predicate
{ expression comparison‑operator expression
| string‑expression [ NOT ] { LIKE | ILIKE | LIKEB | ILIKEB } string-expression
| expression IS [ NOT ] NULL
| expression [ NOT ] IN ( subquery | expression[,... ] )
| expression comparison‑operator [ ANY | SOME ] ( subquery )
| expression comparison‑operator ALL ( subquery )
| expression OR ( subquery )
| [ NOT ] EXISTS ( subquery )
| [ NOT ] IN ( subquery )
}
参数
1 - 逻辑运算符 AND 和 OR
AND 和 OR 逻辑运算符会组合两个条件。当由 AND 关键字联接的两个条件都有匹配时，AND 评估为 TRUE，当由 OR 关键字联接的一个条件有匹配时，则 OR 评估为 TRUE。
OR 子查询（复杂表达式）
Vertica 支持使用 OR 的更复杂的表达式中的子查询，例如：
-
连接表达式中有一个以上子查询：
(SELECT MAX(b) FROM t1) + SELECT (MAX FROM t2) a IN (SELECT a FROM t1) OR b IN (SELECT x FROM t2)
-
连接表达式中的 OR 子句至少包含一个子查询：
a IN (SELECT a FROM t1) OR b IN (SELECT x FROM t2) a IN (SELECT a from t1) OR b = 5
a = (SELECT MAX FROM t2) OR b = 5
-
只有一个子查询，而且它包含在另一个表达式中：
x IN (SELECT a FROM t1) = (x = (SELECT MAX FROM t2) (x IN (SELECT a FROM t1) IS NULL
如何评估 AND 查询
Vertica 会分别对待 AND（连接）运算符分隔的表达式。例如，如果 WHERE 子句为：
WHERE (a IN (SELECT a FROM t1) OR b IN (SELECT x FROM t2)) AND (c IN (SELECT a FROM t1))
则将查询解释为两个连接表达式：
-
(a IN (SELECT a FROM t1) OR b IN (SELECT x FROM t2))
-
(c IN (SELECT a FROM t1))
第一个表达式会被认为是一个复杂的子查询，而第二个表达式则不会。
示例
以下列表显示了过滤 WHERE 子句中复杂条件的几种方法：
-
子查询和非子查询条件之间的 OR 表达式：
=> SELECT x FROM t WHERE x > (SELECT SUM(DISTINCT x) FROM t GROUP BY y) OR x < 9;
-
两个子查询之间的 OR 表达式：
=> SELECT * FROM t WHERE x=(SELECT x FROM t) OR EXISTS(SELECT x FROM tt);
-
子查询表达式：
=> SELECT * FROM t WHERE x=(SELECT x FROM t)+1 OR x<>(SELECT x FROM t)+1;
-
包含 [NOT] IN 子查询的 OR 表达式：
=> SELECT * FROM t WHERE NOT (EXISTS (SELECT x FROM t)) OR x >9;
-
包含 IS [NOT] NULL 子查询的 OR 表达式：
=> SELECT * FROM t WHERE (SELECT * FROM t)IS NULL OR (SELECT * FROM tt)IS NULL;
-
包含 boolean 列和返回 Boolean 数据类型的子查询的 OR 表达式：
=> SELECT * FROM t2 WHERE x = (SELECT x FROM t2) OR x;
注意
要返回 TRUE，OR 的参数必须为 Boolean 数据类型。
-
CASE 语句中的 OR 表达式：
=> SELECT * FROM t WHERE CASE WHEN x=1 THEN x > (SELECT * FROM t)
OR x < (SELECT * FROM t2) END ;
-
分析函数、NULL 处理函数、字符串函数、数学函数等等：
=> SELECT x FROM t WHERE x > (SELECT COALESCE (x,y) FROM t GROUP BY x,y) OR
x < 9;
-
在用户定义的函数（假设 f() 为 1）中：
=> SELECT * FROM t WHERE x > 5 OR x = (SELECT f(x) FROM t);
-
在不同的位置使用圆括号重建查询：
=> SELECT x FROM t WHERE (x = (SELECT x FROM t) AND y = (SELECT y FROM t))
OR (SELECT x FROM t) =1;
-
多列子查询：
=> SELECT * FROM t WHERE (x,y) = (SELECT x,y FROM t) OR x > 5;
-
子查询左侧的常数/NULL：
=> SELECT * FROM t WHERE x > 5 OR 5 = (SELECT x FROM t);
另请参阅
2 - 替代表达式
返回单值（与 IN 子查询返回的值列表不同）的子查询可用于在 SQL 中允许使用表达式的任何位置。它可以是列名称、常数、函数、标量子查询或由运算符或子查询连接的列名称、常数和函数的组合。
例如：
=> SELECT c1 FROM t1 WHERE c1 = ANY (SELECT c1 FROM t2) ORDER BY c1;
=> SELECT c1 FROM t1 WHERE COALESCE((t1.c1 > ANY (SELECT c1 FROM t2)), TRUE);
=> SELECT c1 FROM t1 GROUP BY c1 HAVING
COALESCE((t1.c1 <> ALL (SELECT c1 FROM t2)), TRUE);
多列表达式也受支持：
=> SELECT c1 FROM t1 WHERE (t1.c1, t1.c2) = ALL (SELECT c1, c2 FROM t2);
=> SELECT c1 FROM t1 WHERE (t1.c1, t1.c2) <> ANY (SELECT c1, c2 FROM t2);
如果用作表达式的任何子查询返回了一个以上的行，则 Vertica 将返回查询错误：
=> SELECT c1 FROM t1 WHERE c1 = (SELECT c1 FROM t2) ORDER BY c1;
ERROR: more than one row returned by a subquery used as an expression
另请参阅
3 - 比较运算符
Vertica 支持包含以下任何运算符的 WHERE 子句中的 Boolean 子查询表达式：
>
< >=
<= = <> <=>
WHERE 子句子查询会筛选结果，并采用以下格式：
SELECT <column, ...> FROM <table>
WHERE <condition> (SELECT <column, ...> FROM <table> WHERE <condition>);
只要比较有意义，这些条件可适用于所有数据类型。所有比较运算符均为二元运算符，可返回 TRUE、FALSE 或 UNKNOWN (NULL) 这些值。
表达式可以仅与外查询块中的一个外表相关，而且这些相关表达式可以是比较运算符。
支持以下子查询场景：
SELECT * FROM T1 WHERE T1.x = (SELECT MAX(c1) FROM T2);
SELECT * FROM T1 WHERE T1.x >= (SELECT MAX(c1) FROM T2 WHERE T1.y = T2.c2);
SELECT * FROM T1 WHERE T1.x <= (SELECT MAX(c1) FROM T2 WHERE T1.y = T2.c2);
另请参阅
子查询限制
4 - LIKE 模式匹配
Vertica 支持子查询中的 LIKE 模式匹配条件，并采用以下格式：
string-expression [ NOT ] { LIKE | ILIKE | LIKEB | ILIKEB } string-expression
以下命令搜索公司名称以“Ev”开头的客户，然后返回总计数：
=> SELECT COUNT(*) FROM customer_dimension WHERE customer_name LIKE
(SELECT 'Ev%' FROM customer_dimension LIMIT 1);
count
-------
153
(1 row)
Vertica 还支持将单行子查询用作 LIKEB 谓词和 ILIKEB 谓词的模式实参，例如：
=> SELECT * FROM t1 WHERE t1.x LIKEB (SELECT t2.x FROM t2);
以下符号可替代 LIKE 关键字：
~~ LIKE
~# LIKEB
~~* ILIKE
~#* ILIKEB
!~~ NOT LIKE
!~# NOT LIKEB
!~~* NOT ILIKE
!~#* NOT IILIKEB
注意
上述符号对 ESCAPE 关键字无效。
有关其他示例，请参阅 LIKE 谓词。
5 - 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 子查询。
另请参阅
子查询限制
6 - EXISTS 和 NOT EXISTS
EXISTS 谓词是最常见的谓词之一，可用于构建使用非相关和相关子查询的条件。使用 EXISTS 可以不考虑数量就能识别是否存在关系。例如，如果子查询返回任何行，EXISTS 返回 true，如果子查询没有返回行，[NOT] EXISTS 返回 true。
[NOT] EXISTS 子查询采用以下格式：
expression [ NOT ] EXISTS ( subquery )
如果子查询至少返回了一个行，则认为符合 EXISTS 条件。由于结果仅取决于是否返回了记录，而不是取决于这些记录的内容，因此子查询的输出列表通常没有吸引力。常见的编码规范是按照如下方式编写所有 EXISTS 测试：
EXISTS (SELECT 1 WHERE ...)
在上述片段中，SELECT 1 为查询中所有记录返回了值 1。例如，查询返回了五个记录，它会返回 5 个一。系统不会考虑记录中的实际值；它只要知道是否返回了行。
或者，子查询使用 EXISTS 的选择列表可能包含星号 (*)。您不需要指定列名称，因为查询会测试是否有符合子查询中指定条件的记录。
EXISTS (SELECT * WHERE ...)
注意
-
如果
EXISTS (subquery) 返回至少 1 行，则结果为 TRUE。
-
如果
EXISTS (subquery) 不返回任何行，则结果为 FALSE。
-
如果 NOT EXISTS (subquery) 返回至少 1 行，则结果为 FALSE。
-
如果 NOT EXISTS (subquery) 不返回任何行，则结果为 TRUE。
示例
以下查询检索从任何一家商店购买金额超过 550 美元商品的所有客户的列表：
=> SELECT customer_key, customer_name, customer_state
FROM public.customer_dimension WHERE EXISTS
(SELECT 1 FROM store.store_sales_fact
WHERE customer_key = public.customer_dimension.customer_key
AND sales_dollar_amount > 550)
AND customer_state = 'MA' ORDER BY customer_key;
customer_key | customer_name | customer_state
--------------+--------------------+----------------
14818 | William X. Nielson | MA
18705 | James J. Goldberg | MA
30231 | Sarah N. McCabe | MA
48353 | Mark L. Brown | MA
(4 rows)
使用 EXISTS 子查询还是 IN 子查询，取决于您在外查询块和内查询块中所选择的谓词。例如，要为供应商表中有记录的供应商获取所有商店在 2003 年 1 月 2 日下的所有订单的列表：
=> SELECT store_key, order_number, date_ordered
FROM store.store_orders_fact WHERE EXISTS
(SELECT 1 FROM public.vendor_dimension
WHERE public.vendor_dimension.vendor_key = store.store_orders_fact.vendor_key)
AND date_ordered = '2012-01-02';
store_key | order_number | date_ordered
-----------+--------------+--------------
37 | 2559 | 2012-01-02
16 | 552 | 2012-01-02
35 | 1156 | 2012-01-02
13 | 3885 | 2012-01-02
25 | 554 | 2012-01-02
21 | 2687 | 2012-01-02
49 | 3251 | 2012-01-02
19 | 2922 | 2012-01-02
26 | 1329 | 2012-01-02
40 | 1183 | 2012-01-02
(10 rows)
上述查询查找是否存在下单的供应商和日期。要返回特定的值，而不是简单地确定是否存在，查询需要查找在 2004 年 1 月 4 日达成最佳交易的供应商所下的订单。
=> SELECT store_key, order_number, date_ordered
FROM store.store_orders_fact ord, public.vendor_dimension vd
WHERE ord.vendor_key = vd.vendor_key AND vd.deal_size IN
(SELECT MAX(deal_size) FROM public.vendor_dimension)
AND date_ordered = '2013-01-04';
store_key | order_number | date_ordered
-----------+--------------+--------------
166 | 36008 | 2013-01-04
113 | 66017 | 2013-01-04
198 | 75716 | 2013-01-04
27 | 150241 | 2013-01-04
148 | 182207 | 2013-01-04
9 | 188567 | 2013-01-04
45 | 202416 | 2013-01-04
24 | 250295 | 2013-01-04
121 | 251417 | 2013-01-04
(9 rows)
另请参阅
7 - IN 和 NOT IN
尽管无法使一个单值与一个值集相等，但您可以查看单值是否已在此值集中。对多记录单列子查询使用 IN 子句。子查询返回 IN 或 NOT IN 产生的结果之后，外查询将利用这些结果来返回最终结果。
[NOT] IN 子查询采用以下格式：
{ expression [ NOT ] IN ( subquery )| expression [ NOT ] IN ( expression ) }
传递给 SELECT 语句的 IN 子句的参数数量不受限制；例如：
=> SELECT * FROM tablename WHERE column IN (a, b, c, d, e, ...);
Vertica 还支持两个或多个外表达式同时引用不同内表达式的查询。
=> SELECT * FROM A WHERE (A.x,A.x) IN (SELECT B.x, B.y FROM B);
示例
以下查询使用 VMart 架构显示了同时引用不同内表达式的外表达式的使用情况：
=> SELECT product_description, product_price FROM product_dimension
WHERE (product_dimension.product_key, product_dimension.product_key) IN
(SELECT store.store_orders_fact.order_number,
store.store_orders_fact.quantity_ordered
FROM store.store_orders_fact);
product_description | product_price
-----------------------------+---------------
Brand #72 box of candy | 326
Brand #71 vanilla ice cream | 270
(2 rows)
要查找马萨诸塞州的商店提供的所有产品，请首先创建内查询，然后运行它以确保它可以正常运行。以下查询返回了马萨诸塞州的所有商店：
=> SELECT store_key FROM store.store_dimension WHERE store_state = 'MA';
store_key
-----------
13
31
(2 rows)
然后创建外查询或主查询，指定在马萨诸塞州的商店售出的所有不同产品。此语句使用 IN 谓词将内查询和外查询组合在一起：
=> SELECT DISTINCT s.product_key, p.product_description
FROM store.store_sales_fact s, public.product_dimension p
WHERE s.product_key = p.product_key
AND s.product_version = p.product_version
AND s.store_key IN
(SELECT store_key
FROM store.store_dimension
WHERE store_state = 'MA')
ORDER BY s.product_key;
product_key | product_description
-------------+---------------------------------------
1 | Brand #1 white bread
1 | Brand #4 vegetable soup
3 | Brand #9 wheelchair
5 | Brand #15 cheddar cheese
5 | Brand #19 bleach
7 | Brand #22 canned green beans
7 | Brand #23 canned tomatoes
8 | Brand #24 champagne
8 | Brand #25 chicken nuggets
11 | Brand #32 sausage
... ...
(281 rows)
使用 NOT IN 时，子查询在外查询中返回了一列零值或更多值，其中比较列与子查询返回的任何值都不匹配。使用上一个示例时，NOT IN 返回不是来自马萨诸塞州的所有产品。
注意
如果列没有标记为 NOT NULL，则 Vertica 支持多列 NOT IN 子查询。如果在查询执行期间发现某列中包含 NULL 值，则 Vertica 会返回运行时错误。
同样地，如果任何一个列值为 NULL，则不支持嵌套在另一个表达式中的 IN 子查询。例如，如果在以下语句中，任何一个表的 x 列包含 NULL 值，Vertica 会返回运行时错误：
=> SELECT * FROM t1 WHERE (x IN (SELECT x FROM t2)) IS FALSE;
ERROR: NULL value found in a column used by a subquery
另请参阅