子查询是一种嵌套在其他 SELECT 语句中的 SELECT 语句。嵌套子查询通常称为查询内语句，而包含查询通常称为查询语句或外查询块。子查询返回外查询用作条件的数据，以确定需要检索哪些数据。您可以创建的嵌套子查询的数量没有限制。
与任何查询一样，子查询返回（单列单记录、单列多记录或多列多记录）表中的记录。查询可以是非相关或相关查询。您甚至可以使用它们基于存储在其他数据库表中的值来更新或删除表中的记录。
子查询
- 1: 搜索条件中使用的子查询
- 1.1: 逻辑运算符 AND 和 OR
- 1.2: 替代表达式
- 1.3: 比较运算符
- 1.4: LIKE 模式匹配
- 1.5: ANY 和 ALL
- 1.6: EXISTS 和 NOT EXISTS
- 1.7: IN 和 NOT IN
- 2: SELECT 列表中的子查询
- 3: WITH 子句
- 3.1: WITH 子句的内联展开
- 3.2: WITH 子句的实体化
- 3.3: WITH 子句递归
- 4: 非相关和相关的子查询
- 5: 修整 FROM 子句子查询
- 6: UPDATE 和 DELETE 语句中的子查询
- 7: 子查询示例
- 8: 子查询限制
1 - 搜索条件中使用的子查询
子查询需要用作搜索条件才能过滤结果。它们指定了从包含查询的 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.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);
另请参阅
1.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
另请参阅
1.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);
另请参阅
子查询限制1.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 谓词。
1.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 子查询。
另请参阅
子查询限制1.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)
另请参阅
1.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
另请参阅
2 - SELECT 列表中的子查询
子查询可以在包含查询的 select 列表中使用。以下语句的结果按第一列 (customer_name) 进行排序。您也可以写入 ORDER BY 2，然后指定结果按 select 列表中的子查询进行排序。
=> SELECT c.customer_name, (SELECT AVG(annual_income) FROM customer_dimension
WHERE deal_size = c.deal_size) AVG_SAL_DEAL FROM customer_dimension c
ORDER BY 1;
customer_name | AVG_SAL_DEAL
---------------+--------------
Goldstar | 603429
Metatech | 628086
Metadata | 666728
Foodstar | 695962
Verihope | 715683
Veridata | 868252
Bettercare | 879156
Foodgen | 958954
Virtacom | 991551
Inicorp | 1098835
...
注意
-
select 列表中的标量子查询返回单个行/列值。这些子查询使用 Boolean 比较运算符：=、>、<、<>、<=、>=。
如果查询是相关查询，则当相关生成 0 行时，它将返回 NULL。如果查询返回多行，则查询将在运行时出错，并且 Vertica 会显示错误消息，说明标量子查询必须只能返回 1 行。
-
子查询表达式（例如 [NOT] IN、[NOT] EXISTS、ANY/SOME 或 ALL）总是返回单个评估为 TRUE、FALSE 或 UNKNOWN 的 Boolean 值；子查询自身可以拥有多个行。大多数查询可以为相关或非相关查询。
注意
ALL 子查询不可以为相关子查询。 -
ORDER BY 和 GROUP BY 子句中的子查询受支持，例如，以下语句表示按第一列进行排序，该列是 select-list 子查询：
=> SELECT (SELECT MAX(x) FROM t2 WHERE y=t1.b) FROM t1 ORDER BY 1;
另请参阅
3 - WITH 子句
WITH 子句是较大的主要查询中的伴随查询。Vertica 可以通过两种方式评估 WITH 子句：
-
内联扩展（默认）：当主要查询每次引用每个 WITH 子句时，Vertica 才会对它进行评估。
-
实体化：Vertica 对每个 WITH 子句进行一次评估，将结果存储在临时表中，然后在查询需要时引用此表。
有关语法选项和要求的详细信息，请参阅 WITH 子句。
3.1 - WITH 子句的内联展开
默认情况下，Vertica 使用内联展开评估 WITH 子句。当主要查询每次引用每个 WITH 子句时，Vertica 才会对它进行评估。内联展开通常在查询没有多次引用同一个 WITH 子句时或在内联展开之后可以开展某些本地优化时运行效果最好。
示例
以下示例显示适合用于内联展开的 WITH 子句。在获取 2007 年 12 月 1 日至 7 日发货的所有订单的订单信息的查询中，使用 WITH 子句。
-- Begin WITH
WITH store_orders_fact_new AS(
SELECT * FROM store.store_orders_fact WHERE date_shipped between '2007-12-01' and '2007-12-07')
-- End WITH
-- Begin primary query
SELECT store_key, product_key, product_version, SUM(quantity_ordered*unit_price) AS total_price
FROM store_orders_fact_new
GROUP BY store_key, product_key, product_version
ORDER BY total_price DESC;
store_key | product_key | product_version | total_price
-----------+-------------+-----------------+-------------
232 | 1855 | 2 | 29008
125 | 8500 | 4 | 28812
139 | 3707 | 2 | 28812
212 | 3203 | 1 | 28000
236 | 8023 | 4 | 27548
123 | 10598 | 2 | 27146
34 | 8888 | 4 | 27100
203 | 2243 | 1 | 27027
117 | 13932 | 2 | 27000
84 | 768 | 1 | 26936
123 | 1038 | 1 | 26885
106 | 18932 | 1 | 26864
93 | 10395 | 3 | 26790
162 | 13073 | 1 | 26754
15 | 3679 | 1 | 26675
52 | 5957 | 5 | 26656
190 | 8114 | 3 | 26611
5 | 7772 | 1 | 26588
139 | 6953 | 3 | 26572
202 | 14735 | 1 | 26404
133 | 2740 | 1 | 26312
198 | 8545 | 3 | 26287
221 | 7582 | 2 | 26280
127 | 9468 | 3 | 26224
63 | 8115 | 4 | 25960
171 | 2088 | 1 | 25650
250 | 11210 | 3 | 25608
...
Vertica 处理查询，如下所示：
-
在主要查询中展开对
store_orders_fact_new的 WITH 子句引用。 -
展开 WITH 子句后，评估主要查询。
3.2 - WITH 子句的实体化
实体化启用之后，Vertica 会对每个 WITH 子句进行一次评估，将结果存储在临时表中，然后在查询需要时引用此表。主要查询执行完成之后，Vertica 会将临时表删除。
注意
如果主要查询返回了错误，仅当客户端会话结束之后才可能会删除临时表。如果 WITH 子句非常复杂，例如当 WITH 子句包含 JOIN 和 GROUP BY 子句，并且在主要查询中多次被引用时，实体化可帮助提高性能。
如果实体化已启用，WITH 语句会自动提交用户事务。即使将 EXPLAIN 与 WITH 语句一起使用，也是如此。
启用 WITH 子句实体化
WITH 实体化由配置参数 WithClauseMaterialization 设置，默认设置为 0（禁用）。您可以分别使用 ALTER DATABASE 和 ALTER SESSION，通过在数据库和会话级别设置 WithClauseMaterialization 来启用和禁用实体化：
-
数据库：
=> ALTER DATABASE db-spec SET PARAMETER WithClauseMaterialization={ 0 | 1 }; => ALTER DATABASE db-spec CLEAR PARAMETER WithClauseMaterialization; -
会话：参数设置将继续有效，直到以显式方式清除它，或者会话结束。
=> ALTER SESSION SET PARAMETER WithClauseMaterialization={ 0 | 1 }; => ALTER SESSION CLEAR PARAMETER WithClauseMaterialization;
您还可以使用提示 ENABLE_WITH_CLAUSE_MATERIALIZATION 为单个查询启用 WITH 实体化。如果查询返回，则会自动清除实体化。例如：
=> WITH /*+ENABLE_WITH_CLAUSE_MATERIALIZATION */ revenue AS (
SELECT vendor_key, SUM(total_order_cost) AS total_revenue
FROM store.store_orders_fact
GROUP BY vendor_key ORDER BY 1)
...
使用 EE5 临时关系处理 WITH 子句
默认情况下，当重用 WITH 子句查询时，Vertica 将这些 WITH 子句查询输出保存在 EE5 临时关系中。不过，可以更改此选项。可以通过以下设置方式，使用配置参数 EnableWITHTempRelReuseLimit 来设置 EE5 临时关系支持：
-
0：禁用此功能。
-
1：将所有 WITH 子句查询强制保存到 EE5 临时关系中，无论它们是否被重用。
-
2（默认值）：仅将重用的 WITH 子句查询保存到 EE5 临时关系中。
-
3 及 3 以上：仅当使用 WITH 子句查询至少达到此次数时才将其保存到 EE5 临时关系中。
可以分别使用 ALTER DATABASE 和 ALTER SESSION，在数据库和会话级别设置 EnableWITHTempRelReuseLimit。当 WithClauseMaterialization 设置为 1 时，该设置将覆盖任何 EnableWITHTempRelReuseLimit 设置。
请注意，对于具有复杂类型的 WITH 查询，禁用临时关系。
示例
以下示例显示适合用于实体化的 WITH 子句。查询获取在所有订单中拥有最高合并订单成本的供应商的数据：
-- Enable materialization
=> ALTER SESSION SET PARAMETER WithClauseMaterialization=1;
-- Define WITH clause
=> WITH revenue AS (
SELECT vendor_key, SUM(total_order_cost) AS total_revenue
FROM store.store_orders_fact
GROUP BY vendor_key ORDER BY 1)
-- End WITH clause
-- Primary query
=> SELECT vendor_name, vendor_address, vendor_city, total_revenue
FROM vendor_dimension v, revenue r
WHERE v.vendor_key = r.vendor_key AND total_revenue = (SELECT MAX(total_revenue) FROM revenue )
ORDER BY vendor_name;
vendor_name | vendor_address | vendor_city | total_revenue
------------------+----------------+-------------+---------------
Frozen Suppliers | 471 Mission St | Peoria | 49877044
(1 row)
Vertica 按以下方式处理此查询：
-
WITH 子句
revenue通过store.store_orders_fact表评估 SELECT 语句。 -
revenue子句的结果存储在本地临时表中。 -
无论何时引用
revenue子句，都会使用表中存储的结果。 -
查询执行完成之后，临时表会删除。
3.3 - WITH 子句递归
包含 RECURSIVE 选项的 WITH 子句可以重复执行 UNION 或 UNION ALL 查询，从而迭代其自身的输出。递归查询在处理分层结构（例如，经理下属关系）或树状结构数据（如分类法）等自引用数据时十分有用。
配置参数 WithClauseRecursionLimit（默认设置为 8）将设置递归的最大深度。您可以分别使用 ALTER DATABASE 和 ALTER SESSION 在数据库和会话范围内设置此参数。递归将会继续，直到达到配置的最大深度为止，或者直到最后一次迭代返回没有数据为止。
可以按如下方式指定递归 WITH 子句：
WITH [ /*+ENABLE_WITH_CLAUSE_MATERIALIZATION*/ ] RECURSIVE
cte‑identifier [ ( column-aliases ) ] AS (
non-recursive-term
UNION [ ALL ]
recursive-term
)
非递归项和递归项由 UNION 或 UNION ALL 分隔：
-
non-recursive-term 查询将其结果集设置在 cte-identifier，在 recursive-term 中递归。
-
UNION 语句的 recursive-term 以递归方式迭代其自身输出。当递归完成时，所有迭代的结果均会编译并在 cte-identifier 中设置。
例如：
=> ALTER SESSION SET PARAMETER WithClauseRecursionLimit=4; -- maximum recursion depth = 4
=> WITH RECURSIVE nums (n) AS (
SELECT 1 -- non-recursive (base) term
UNION ALL
SELECT n+1 FROM nums -- recursive term
)
SELECT n FROM nums; -- primary query
此简单查询按如下方式执行：
-
执行 WITH RECURSIVE 子句：
-
评估非递归项 SELECT 1，并将结果集 (1) 放入
nums。 -
迭代 UNION ALL 查询 (SELECT n+1)，直到迭代次数大于配置参数 WithClauseRecursionLimit。
-
合并所有 UNION 查询的结果，并将结果集设置在
nums中，然后退出到主要查询。
-
-
执行主要查询 SELECT n FROM nums：
n --- 1 2 3 4 5 (5 rows)
在本例中，根据 WithClauseRecursionLimit，WITH RECURSIVE 子句在四次迭代后退出。如果将 WithClauseRecursionLimit 还原为其默认值 8，则子句在八次迭代后退出：
=> ALTER SESSION CLEAR PARAMETER WithClauseRecursionLimit;
=> WITH RECURSIVE nums (n) AS (
SELECT 1
UNION ALL
SELECT n+1 FROM nums
)
SELECT n FROM nums;
n
---
1
2
3
4
5
6
7
8
9
(9 rows)
重要
请务必将 WithClauseRecursionLimit 设置为遍历最深层次结构所需的高度。Vertica 对此参数没有设置限制；但是，较高的值会产生相当大的开销，从而对性能产生不利影响并耗尽系统资源。
如果需要较高的递归计数，则考虑启用实体化。有关详细信息，请参阅 WITH RECURSIVE 实体化。
限制
存在以下限制：
-
非递归项的 SELECT 列表不能包含通配符
*（星号）或函数 MATCH_COLUMNS。 -
递归项只能引用目标 CTE 一次。
-
递归引用不能出现在外联接中。
-
递归引用不能出现在子查询中。
-
WITH 子句不支持 UNION 选项 ORDER BY、LIMIT 和 OFFSET。
示例
一家小型软件公司维护以下有关员工及其经理的数据：
=> SELECT * FROM personnel.employees ORDER BY emp_id;
emp_id | fname | lname | section_id | section_name | section_leader | leader_id
--------+-----------+-----------+------------+---------------------+------------------+-----------
0 | Stephen | Mulligan | 0 | | |
1 | Michael | North | 201 | Development | Zoe Black | 3
2 | Megan | Berry | 202 | QA | Richard Chan | 18
3 | Zoe | Black | 101 | Product Development | Renuka Patil | 24
4 | Tim | James | 203 | IT | Ebuka Udechukwu | 17
5 | Bella | Tucker | 201 | Development | Zoe Black | 3
6 | Alexandra | Climo | 202 | QA | Richard Chan | 18
7 | Leonard | Gray | 203 | IT | Ebuka Udechukwu | 17
8 | Carolyn | Henderson | 201 | Development | Zoe Black | 3
9 | Ryan | Henderson | 201 | Development | Zoe Black | 3
10 | Frank | Tucker | 205 | Sales | Benjamin Glover | 29
11 | Nathan | Ferguson | 102 | Sales Marketing | Eric Redfield | 28
12 | Kevin | Rampling | 101 | Product Development | Renuka Patil | 24
13 | Tuy Kim | Duong | 201 | Development | Zoe Black | 3
14 | Dwipendra | Sing | 204 | Tech Support | Sarah Feldman | 26
15 | Dylan | Wijman | 206 | Documentation | Kevin Rampling | 12
16 | Tamar | Sasson | 207 | Marketing | Nathan Ferguson | 11
17 | Ebuka | Udechukwu | 101 | Product Development | Renuka Patil | 24
18 | Richard | Chan | 101 | Product Development | Renuka Patil | 24
19 | Maria | del Rio | 201 | Development | Zoe Black | 3
20 | Hua | Song | 204 | Tech Support | Sarah Feldman | 26
21 | Carmen | Lopez | 204 | Tech Support | Sarah Feldman | 26
22 | Edgar | Mejia | 206 | Documentation | Kevin Rampling | 12
23 | Riad | Salim | 201 | Development | Zoe Black | 3
24 | Renuka | Patil | 100 | Executive Office | Stephen Mulligan | 0
25 | Rina | Dsouza | 202 | QA | Richard Chan | 18
26 | Sarah | Feldman | 101 | Product Development | Renuka Patil | 24
27 | Max | Mills | 102 | Sales Marketing | Eric Redfield | 28
28 | Eric | Redfield | 100 | Executive Office | Stephen Mulligan | 0
29 | Benjamin | Glover | 102 | Sales Marketing | Eric Redfield | 28
30 | Dominic | King | 205 | Sales | Benjamin Glover | 29
32 | Ryan | Metcalfe | 206 | Documentation | Kevin Rampling | 12
33 | Piers | Paige | 201 | Development | Zoe Black | 3
34 | Nicola | Kelly | 207 | Marketing | Nathan Ferguson | 11
(34 rows)
您可以通过 WITH RECURSIVE 查询此数据以了解员工与经理的关系。例如，以下查询的 WITH RECURSIVE 子句获取员工 Eric Redfield（包括他的所有直接下属和间接下属员工）的员工-经理关系：
WITH RECURSIVE managers (employeeID, employeeName, sectionID, section, lead, leadID)
AS (SELECT emp_id, fname||' '||lname, section_id, section_name, section_leader, leader_id
FROM personnel.employees WHERE fname||' '||lname = 'Eric Redfield'
UNION
SELECT emp_id, fname||' '||lname AS employee_name, section_id, section_name, section_leader, leader_id FROM personnel.employees e
JOIN managers m ON m.employeeID = e.leader_id)
SELECT employeeID, employeeName, lead AS 'Reports to', section, leadID from managers ORDER BY sectionID, employeeName;
WITH RECURSIVE 子句定义了 CTE managers，然后分两个阶段执行：
-
非递归项使用从
personnel.employees查询的数据填充managers。 -
递归项的 UNION 查询迭代它自己的输出，直到在第四个循环中找不到更多数据。然后，所有迭代结果进行编译并设置在
managers中，WITH CLAUSE 退出到主要查询。
主要查询从 managers 返回三个级别的数据（每个递归迭代一个级别）：
类似地，以下查询遍历相同的数据以获取员工 Richard Chan（他在公司管理层中比 Eric Redfield 低一级）的所有员工-经理关系：
WITH RECURSIVE managers (employeeID, employeeName, sectionID, section, lead, leadID)
AS (SELECT emp_id, fname||' '||lname, section_id, section_name, section_leader, leader_id
FROM personnel.employees WHERE fname||' '||lname = 'Richard Chan'
UNION
SELECT emp_id, fname||' '||lname AS employee_name, section_id, section_name, section_leader, leader_id FROM personnel.employees e
JOIN managers m ON m.employeeID = e.leader_id)
SELECT employeeID, employeeName, lead AS 'Reports to', section, leadID from managers ORDER BY sectionID, employeeName;
WITH RECURSIVE 子句像以前一样执行，但这次它在两次迭代后找不到更多数据而退出。因此，主要查询从 managers 返回两个级别的数据：
WITH RECURSIVE 实体化
默认情况下，实体化处于禁用状态。在这种情况下，Vertica 将 WITH RECURSIVE 查询重写为子查询，其数量与所需的递归级别一样多。
如果递归非常深，则大量查询重写可能会产生相当大的开销，从而对性能产生不利影响并耗尽系统资源。在这种情况下，请考虑使用配置参数 WithClauseMaterialization 或提示 ENABLE_WITH_CLAUSE_MATERIALIZATION 来启用实体化。在任何一种情况下，来自所有递归级别的中间结果集都将写入本地临时表。当递归完成时，所有临时表中的中间结果都进行编译并传递给主要查询。
注意
如果无法实体化，您可以通过将 EXECUTIONPARALLELISM 参数设置为 1 来提高处理递归查询的资源池的吞吐量。4 - 非相关和相关的子查询
子查询可以分为以下两种类型：
-
非相关（简单）子查询获取结果不需要依赖其包含（外）语句。
-
相关子查询需要来自其外查询的值才能执行。
非相关子查询
非相关子查询的执行不需要依赖外查询。子查询首先执行，然后将结果传递给外查询，例如：
=> SELECT name, street, city, state FROM addresses WHERE state IN (SELECT state FROM states);
Vertica 按以下方式执行此查询：
-
执行子查询
SELECT state FROM states（粗体）。 -
将子查询结果传递给外查询。
如果子查询解析为一个单行，查询的 WHERE 和 HAVING 子句可以指定非相关子句，如下所示：
在 WHERE 子句中
=> SELECT COUNT(*) FROM SubQ1 WHERE SubQ1.a = (SELECT y from SubQ2);
在 HAVING 子句中
=> SELECT COUNT(*) FROM SubQ1 GROUP BY SubQ1.a HAVING SubQ1.a = (SubQ1.a & (SELECT y from SubQ2)
相关子查询
相关子查询通常在执行之前从外查询获取值。当子查询返回时，它将其结果传递给外查询。
注意
使用外联接可获得与相关子查询相同的效果。在以下示例中，子查询需要外查询中 addresses.state 列的值。
=> SELECT name, street, city, state FROM addresses
WHERE EXISTS (SELECT * FROM states WHERE states.state = addresses.state);
Vertica 按以下方式执行此查询：
-
子查询提取并评估外子查询记录中的每个
addresses.state值。 -
然后，查询（使用 EXISTS 谓词）检查内部（相关）子查询中的地址。
-
由于它使用 EXISTS 谓词，因此查询会在找到第一个匹配项时停止处理。
Vertica 执行此查询时，会将完整查询转换为 JOIN WITH SIPS。
5 - 修整 FROM 子句子查询
FROM 子句子查询的评估通常早于它们的包含查询。在某些情况下，优化器修整FROM子句查询，使查询可以更高效地执行。
例如，为了针对以下语句创建查询计划，Vertica 查询优化器会先评估表 t1 中的所有记录，然后再评估表 t0 中的记录：
=> SELECT * FROM (SELECT a, MAX(a) AS max FROM (SELECT * FROM t1) AS t0 GROUP BY a);
使用上述查询，优化器可以在内部修整它，如下所示：
=> SELECT * FROM (SELECT a, MAX(a) FROM t1 GROUP BY a) AS t0;
两个查询返回了相同的结果，但修整后的查询的运行速度更快。
修整视图
当查询的 FROM 子句指定视图时，优化器通过将视图替换为视图封装的查询来扩展视图。如果视图包含符合修整条件的子查询，则优化器会生成一个查询计划来修整这些子查询。
修整限制
如果子查询或视图包含以下元素之一，则优化器无法创建修整查询计划：
-
聚合函数
-
分析函数
-
外联接（左外联接、右外联接或全外联接）
-
GROUP BY、ORDER BY或HAVING子句 -
DISTINCT关键字 -
LIMIT或OFFSET子句 -
UNION、EXCEPT或INTERSECT子句 -
EXISTS子查询
示例
如果谓词适用于视图或子查询，只要在修整之前评估谓词，即可对修整操作进行优化。下面举两个示例。
视图修整
在本例中，视图 v1 定义如下：
=> CREATE VIEW v1 AS SELECT * FROM a;
以下查询指定此视图：
=> SELECT * FROM v1 JOIN b ON x=y WHERE x > 10;
在没有修整的情况下，优化器按如下方式评估查询：
-
评估子查询。
-
应用谓词
WHERE x > 10。
相反，优化器可以通过在评估子查询之前应用谓词来创建修整查询计划。这减少了优化器的工作，因为它只将记录 WHERE x > 10 返回到包含查询。
Vertica 在内部转换了上述查询，如下所示：
=> SELECT * FROM (SELECT * FROM a) AS t1 JOIN b ON x=y WHERE x > 10;
然后，优化器修整查询：
=> SELECT * FROM a JOIN b ON x=y WHERE x > 10;
子查询修整
以下示例演示 Vertica 如何在 WHERE 子句 IN 子查询中转换 FROM 子句子查询。给定以下查询：
=> SELECT * FROM a
WHERE b IN (SELECT b FROM (SELECT * FROM t2)) AS D WHERE x=1;
优化器将其修整如下：
=> SELECT * FROM a
WHERE b IN (SELECT b FROM t2) AS D WHERE x=1;
另请参阅
子查询限制6 - UPDATE 和 DELETE 语句中的子查询
您可以在 UPDATE 和 DELETE 语句中嵌套子查询。
UPDATE 子查询
您可以通过在 UPDATE 语句中嵌套子查询，根据其他表中的值更新一个表中的记录。下面的示例通过几个非相关子查询说明了这一点。您可以使用下表重现此示例：
=> CREATE TABLE addresses(cust_id INTEGER, address VARCHAR(2000));
CREATE TABLE
dbadmin=> INSERT INTO addresses VALUES(20,'Lincoln Street'),(30,'Booth Hill Road'),(30,'Beach Avenue'),(40,'Mt. Vernon Street'),(50,'Hillside Avenue');
OUTPUT
--------
5
(1 row)
=> CREATE TABLE new_addresses(new_cust_id integer, new_address Boolean DEFAULT 'T');
CREATE TABLE
dbadmin=> INSERT INTO new_addresses VALUES (20),(30),(80);
OUTPUT
--------
3
(1 row)
=> INSERT INTO new_addresses VALUES (60,'F');
OUTPUT
--------
1
=> COMMIT;
COMMIT
对这些表的查询会返回以下结果：
=> SELECT * FROM addresses;
cust_id | address
---------+-------------------
20 | Lincoln Street
30 | Beach Avenue
30 | Booth Hill Road
40 | Mt. Vernon Street
50 | Hillside Avenue
(5 rows)
=> SELECT * FROM new_addresses;
new_cust_id | new_address
-------------+-------------
20 | t
30 | t
80 | t
60 | f
(4 rows)
-
以下 UPDATE 语句使用非相关子查询来联接 customer ID 上的
new_addresses和addresses记录。UPDATE 在联接的addresses记录中设置 'New Address' 值。语句输出表明更新了三行：=> UPDATE addresses SET address='New Address' WHERE cust_id IN (SELECT new_cust_id FROM new_addresses WHERE new_address='T'); OUTPUT -------- 3 (1 row) -
查询
addresses表，以查看匹配客户 ID 20 和 30 的更改。客户 ID 40 和 50 的地址未更新：=> SELECT * FROM addresses; cust_id | address ---------+------------------- 40 | Mt. Vernon Street 50 | Hillside Avenue 20 | New Address 30 | New Address 30 | New Address (5 rows) =>COMMIT; COMMIT
DELETE 子查询
您可以通过在 DELETE 语句中嵌套子查询，根据其他表中的值删除一个表中的记录。
例如，您想从之前用于更新 addresses 中的记录的 new_addresses 中移除记录。以下 DELETE 语句使用非相关子查询来联接 customer ID 上的 new_addresses 和 addresses 记录。然后它从表 new_addresses 中删除联接的记录：
=> DELETE FROM new_addresses
WHERE new_cust_id IN (SELECT cust_id FROM addresses WHERE address='New Address');
OUTPUT
--------
2
(1 row)
=> COMMIT;
COMMIT
查询 new_addresses 以确认记录已被删除：
=> SELECT * FROM new_addresses;
new_cust_id | new_address
-------------+-------------
60 | f
80 | t
(2 rows)
7 - 子查询示例
此主题显示了一些可以编写的子查询。示例使用 VMart 示例数据库。
单行子查询
单行子查询将与单行比较运算符（=、>=、<=、<> 和 <=>）一起使用，而且正好返回一行。
例如，以下查询在 Vmart 数据库中检索工作时间最长的员工的姓名和雇佣日期：
=> SELECT employee_key, employee_first_name, employee_last_name, hire_date
FROM employee_dimension
WHERE hire_date = (SELECT MIN(hire_date) FROM employee_dimension);
employee_key | employee_first_name | employee_last_name | hire_date
--------------+---------------------+--------------------+------------
2292 | Mary | Bauer | 1956-01-11
(1 row)
多行子查询
多行子查询返回多个记录。
例如，以下 IN 子句子查询返回了六个地区薪水最高的员工的姓名：
=> SELECT employee_first_name, employee_last_name, annual_salary, employee_region
FROM employee_dimension WHERE annual_salary IN
(SELECT MAX(annual_salary) FROM employee_dimension GROUP BY employee_region)
ORDER BY annual_salary DESC;
employee_first_name | employee_last_name | annual_salary | employee_region
---------------------+--------------------+---------------+-------------------
Alexandra | Sanchez | 992363 | West
Mark | Vogel | 983634 | South
Tiffany | Vu | 977716 | SouthWest
Barbara | Lewis | 957949 | MidWest
Sally | Gauthier | 927335 | East
Wendy | Nielson | 777037 | NorthWest
(6 rows)
多列子查询
多列子查询返回一个或多个列。有时，子查询的结果集在列对列比较和行对行比较的包含查询中进行评估。
注意
多列子查询可以使用 <>、!= 和 = 运算符，但不可以使用 <、>、<=、>= 运算符。您可以将一些多列子查询替换为联接，也可以将联接替换为多列子查询。例如，以下两个查询会检索向马萨诸塞州客户在线售出的所有产品的销售事务，然后返回了相同的结果集。唯一的区别在于，第一个查询编写为联接，第二个查询编写为子查询。
以下查询返回各地区薪水超过平均薪水的所有员工：
=> SELECT e.employee_first_name, e.employee_last_name, e.annual_salary,
e.employee_region, s.average
FROM employee_dimension e,
(SELECT employee_region, AVG(annual_salary) AS average
FROM employee_dimension GROUP BY employee_region) AS s
WHERE e.employee_region = s.employee_region AND e.annual_salary > s.average
ORDER BY annual_salary DESC;
employee_first_name | employee_last_name | annual_salary | employee_region | average
---------------------+--------------------+---------------+-----------------+------------------
Doug | Overstreet | 995533 | East | 61192.786013986
Matt | Gauthier | 988807 | South | 57337.8638902996
Lauren | Nguyen | 968625 | West | 56848.4274914089
Jack | Campbell | 963914 | West | 56848.4274914089
William | Martin | 943477 | NorthWest | 58928.2276119403
Luigi | Campbell | 939255 | MidWest | 59614.9170454545
Sarah | Brown | 901619 | South | 57337.8638902996
Craig | Goldberg | 895836 | East | 61192.786013986
Sam | Vu | 889841 | MidWest | 59614.9170454545
Luigi | Sanchez | 885078 | MidWest | 59614.9170454545
Michael | Weaver | 882685 | South | 57337.8638902996
Doug | Pavlov | 881443 | SouthWest | 57187.2510548523
Ruth | McNulty | 874897 | East | 61192.786013986
Luigi | Dobisz | 868213 | West | 56848.4274914089
Laura | Lang | 865829 | East | 61192.786013986
...
您也可以在 FROM、WHERE 和 HAVING 子句中使用 EXCEPT、INTERSECT 和 UNION [ALL] 关键字。
以下子查询返回了有关通过商店或在线销售渠道购买商品且购买金额超过 500 美元的所有康涅狄格州客户的信息：
=> SELECT DISTINCT customer_key, customer_name FROM public.customer_dimension
WHERE customer_key IN (SELECT customer_key FROM store.store_sales_fact
WHERE sales_dollar_amount > 500
UNION ALL
SELECT customer_key FROM online_sales.online_sales_fact
WHERE sales_dollar_amount > 500)
AND customer_state = 'CT';
customer_key | customer_name
--------------+------------------
200 | Carla Y. Kramer
733 | Mary Z. Vogel
931 | Lauren X. Roy
1533 | James C. Vu
2948 | Infocare
4909 | Matt Z. Winkler
5311 | John Z. Goldberg
5520 | Laura M. Martin
5623 | Daniel R. Kramer
6759 | Daniel Q. Nguyen
...
HAVING 子句子查询
HAVING 子句与 GROUP BY 子句一起使用可过滤 GROUP BY 返回的 select-list 记录。HAVING 子句子查询必须使用布尔比较运算符：=, >, <, <>, <=, >= 并采用以下格式：
SELECT <column, ...>
FROM <table>
GROUP BY <expression>
HAVING <expression>
(SELECT <column, ...>
FROM <table>
HAVING <expression>);
例如，以下语句使用 VMart 数据库，然后返回购买低脂产品的客户数量。请注意，GROUP BY 子句必不可少，因为查询使用了聚合 (COUNT)。
=> SELECT s.product_key, COUNT(s.customer_key) FROM store.store_sales_fact s
GROUP BY s.product_key HAVING s.product_key IN
(SELECT product_key FROM product_dimension WHERE diet_type = 'Low Fat');
子查询首先返回所有低脂产品的产品键，然后由外查询统计购买这些产品的客户总数量。
product_key | count
-------------+-------
15 | 2
41 | 1
66 | 1
106 | 1
118 | 1
169 | 1
181 | 2
184 | 2
186 | 2
211 | 1
229 | 1
267 | 1
289 | 1
334 | 2
336 | 1
(15 rows)
8 - 子查询限制
以下限制适用于 Vertica 子查询：
-
子查询不可在
CREATE PROJECTION语句的定义查询中使用。 -
子查询可以在
SELECT列表中使用，但如果子查询不是包含查询的GROUP BY子句的组成部分，则GROUP BY或聚合函数不可在查询中使用。例如，以下两个语句会返回错误消息：=> SELECT y, (SELECT MAX(a) FROM t1) FROM t2 GROUP BY y; ERROR: subqueries in the SELECT or ORDER BY are not supported if the subquery is not part of the GROUP BY => SELECT MAX(y), (SELECT MAX(a) FROM t1) FROM t2; ERROR: subqueries in the SELECT or ORDER BY are not supported if the query has aggregates and the subquery is not part of the GROUP BY -
支持在
UPDATE语句中使用子查询，但存在以下例外：-
不能使用
SET column = {expression}来指定子查询。 -
UPDATE列表中指定的表也不能出现在FROM子句中（没有自联接）。
-
-
FROM子句子查询需要别名，但表不需要别名。如果表没有别名，查询必须将其中的列命名为 table-name.column-name。但是，在查询中的所有表之间均保持唯一的列名不需要由其表名来限定。 -
如果
ORDER BY子句包含在FROM子句子查询中，而不是包含在包含查询中，则查询可能会返回意外的排序结果。发生这种情况是因为 Vertica 数据来自多个节点，因此无法保证排序顺序，除非外部查询块指定ORDER BY子句。这种行为符合 SQL 标准，但它有别于其他数据库。 -
多列子查询不能使用 <、>、<=、>= 比较运算符。它们可以使用 <>、!= 和 = 比较运算符。
-
WHERE和HAVING子句子查询必须使用布尔比较运算符：=、>、<、<>、<=、>=。这些子查询可以是非相关和相关子查询。 -
[NOT] IN和ANY子查询（嵌套在另一个表达式中）在以下情况下不受支持：任一列值为 NULL。例如，在以下语句中，如果表t1或t2中的列 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 -
Vertica 将在标量子查询（返回多行）的子查询运行期间返回错误消息。
-
只要子查询不是相关子查询，聚合和 GROUP BY 子句即可在这些子查询中使用。
-
ALL和[NOT] IN下的相关表达式不受支持。 -
OR下的相关表达式不受支持。 -
对于使用等式 (=) 谓词联接的子查询，才仅允许使用多重相关。但是，不允许在相关子查询中使用
IN/NOT IN、EXISTS/NOT EXISTS谓词：=> SELECT t2.x, t2.y, t2.z FROM t2 WHERE t2.z NOT IN (SELECT t1.z FROM t1 WHERE t1.x = t2.x); ERROR: Correlated subquery with NOT IN is not supported -
如果子查询引用了当前外部查询块中的列，则
WHERE子句中最多只能使用一个级别的相关子查询。例如，以下查询不受支持，因为t2.x = t3.x子查询只能在外部查询中引用表t1，使其成为相关表达式，因为t3.x具有两个以上的级别：=> SELECT t3.x, t3.y, t3.z FROM t3 WHERE t3.z IN ( SELECT t1.z FROM t1 WHERE EXISTS ( SELECT 'x' FROM t2 WHERE t2.x = t3.x) AND t1.x = t3.x); ERROR: More than one level correlated subqueries are not supported如果按如下所示进行重写，则支持该查询：
=> SELECT t3.x, t3.y, t3.z FROM t3 WHERE t3.z IN (SELECT t1.z FROM t1 WHERE EXISTS (SELECT 'x' FROM t2 WHERE t2.x = t1.x) AND t1.x = t3.x);