查询是一种数据库操作，可从一个或多个表或视图中检索数据。在 Vertica 中，查询是最高级别的 SELECT 语句，嵌套在其他 SQL 语句中的查询称为子查询。
Vertica 的设计目的是运行在其他数据库中运行的相同 SQL 标准查询。但是，Vertica 查询与在其他关系数据库管理系统中使用的查询存在一些区别。
Vertica 事务模型不同于 SQL 标准，它对查询性能会产生深远影响。可以：
对在任何特定日期和时间生成的数据库静态备份运行查询。这样做可避免持有锁或阻止其他数据库操作。
使用标准 SQL 隔离级别子集，并访问用户 会话模式（读取/写入或只读）。
在 Vertica 中，语句是 SQL 查询的主要结构。每个语句以分号结束，您可以编写以分号分隔的多个查询；例如：
=> CREATE TABLE t1( ..., date_col date NOT NULL, ...);
=> CREATE TABLE t2( ..., state VARCHAR NOT NULL, ...);
Vertica 可以执行历史查询，这些查询针对在特定时间戳或时期获取的数据库快照执行。历史查询可用于评估并可能恢复已删除但尚未清除的数据。
您可以通过限定包含
AT epoch 子句的
SELECT 语句来指定历史查询，其中 epoch 为以下内容之一：
EPOCH LATEST：返回数据直到当前时期（但不包括当前时期）。结果集包括来自最新提交的 DML 事务的数据。
EPOCH integer：返回数据直到 integer 指定的时期（包括该指定时期）。
TIME 'timestamp'：从 timestamp 指定的时期返回数据。
有关 Vertica 如何使用时期的详细信息，请参阅时期。
历史查询仅返回指定时期的数据。由于它们不返回最新数据，因此历史查询不包含锁定或阻塞写操作。
查询结果对于事务而言是私有的，并且只在会话时长内有效。无论事务隔离级别如何，查询执行都是相同的。
指定时期或指定时间戳的时期不能小于 Ancient History Mark 时期。
Vertica 不支持对临时表运行历史查询。
您可以使用 CREATE TEMPORARY TABLE 语句实施某些查询，其步骤如下：
创建一个或多个临时表。
执行查询，然后将结果集存储在临时表中。
使用临时表执行主要查询，就好像临时表是 逻辑架构的正常组成部分一样。
有关详细信息，请参阅《SQL 参考手册》中的CREATE TEMPORARY TABLE。
所有 DML（数据操作语言）语句都可包含查询。此部分介绍 Vertica 中的部分查询类型，其他详细信息将在后续几节介绍。
简单查询包含对一个表执行的查询。以下查询会查找产品表中的产品键和 SKU 编号，处理它只需要很少的工作量。
=> SELECT product_key, sku_number FROM public.product_dimension;
product_key | sku_number
-------------+-----------
43 | SKU-#129
87 | SKU-#250
42 | SKU-#125
49 | SKU-#154
37 | SKU-#107
36 | SKU-#106
86 | SKU-#248
41 | SKU-#121
88 | SKU-#257
40 | SKU-#120
(10 rows)
表可以包含数组。您可以选择整个数组列、其索引或应用于数组的函数的结果。有关详细信息，请参阅数组和集（集合）。
联接使用关系运算符合并两个或两个以上表中的信息。查询的 ON 子句会指定表的合并方式，例如通过将外键与主键相匹配。在以下示例中，查询通过联接商店架构的销售事实表和销售表中的商店键 ID，请求交易量大于 70 的商店的名称。
=> SELECT store_name, COUNT(*) FROM store.store_sales_fact
JOIN store.store_dimension ON store.store_sales_fact.store_key = store.store_dimension.store_key
GROUP BY store_name HAVING COUNT(*) > 70 ORDER BY store_name;
store_name | count
------------+-------
Store49 | 72
Store83 | 78
(2 rows)
有关更多详细信息，请参阅联接。另请参阅子查询示例中的“多列子查询”部分。
交叉联接又称为笛卡儿积，它会将一个表中所有记录与另一个表中所有记录联接在一起。当表之间没有联接键来限制记录时，则会产生交叉联接。例如，以下查询返回了供应商表和商店表中供应商和商店名称的所有实例。
=> SELECT vendor_name, store_name FROM public.vendor_dimension
CROSS JOIN store.store_dimension;
vendor_name | store_name
--------------------+------------
Deal Warehouse | Store41
Deal Warehouse | Store12
Deal Warehouse | Store46
Deal Warehouse | Store50
Deal Warehouse | Store15
Deal Warehouse | Store48
Deal Warehouse | Store39
Sundry Wholesale | Store41
Sundry Wholesale | Store12
Sundry Wholesale | Store46
Sundry Wholesale | Store50
Sundry Wholesale | Store15
Sundry Wholesale | Store48
Sundry Wholesale | Store39
Market Discounters | Store41
Market Discounters | Store12
Market Discounters | Store46
Market Discounters | Store50
Market Discounters | Store15
Market Discounters | Store48
Market Discounters | Store39
Market Suppliers | Store41
Market Suppliers | Store12
Market Suppliers | Store46
Market Suppliers | Store50
Market Suppliers | Store15
Market Suppliers | Store48
Market Suppliers | Store39
... | ...
(4000 rows)
此示例的输出已截断，因为这个特殊的交叉联接返回了几千行。另请参阅交叉联接。
子查询是一种嵌套在其他查询中的查询。在以下示例中，我们希望列出脂肪含量最高的所有产品。内查询（子查询）向外查询块（包含查询）返回了所有食品产品中脂肪含量最高的产品。然后，外查询使用这些信息返回脂肪含量最高的产品的名称。
=> SELECT product_description, fat_content FROM public.product_dimension
WHERE fat_content IN
(SELECT MAX(fat_content) FROM public.product_dimension
WHERE category_description = 'Food' AND department_description = 'Bakery')
LIMIT 10;
product_description | fat_content
-------------------------------------+-------------
Brand #59110 hotdog buns | 90
Brand #58107 english muffins | 90
Brand #57135 english muffins | 90
Brand #54870 cinnamon buns | 90
Brand #53690 english muffins | 90
Brand #53096 bagels | 90
Brand #50678 chocolate chip cookies | 90
Brand #49269 wheat bread | 90
Brand #47156 coffee cake | 90
Brand #43844 corn muffins | 90
(10 rows)
有关详细信息，请参阅子查询。
使用 ORDER BY 子句可以对查询返回的行进行排序。
在不同计算机上运行某些查询得到的结果可能不同，其原因如下：
由于精确度原因，在 FLOAT 类型上分区会返回具有不确定性的结果，尤其当数量接近于另一种类型时，例如输出范围非常小的 RADIANS() 函数的结果。
如果您必须通过不属于 INTEGER 类型的数据进行分区，要想获得具有确定性的结果，请使用 NUMERIC 。
大多数分析（和分析聚合，例如作为例外的 MIN()/MAX()/SUM()/COUNT()/AVG()）依赖于输入数据的唯一顺序来获取具有确定性的结果。如果分析 window-order 子句无法解析数据中的关系，每次运行查询时结果都不相同。
例如，在以下查询中，分析 ORDER BY 没有在查询中包含第一列 promotion_key。因此，对于 AVG(RADIANS(cost_dollar_amount)), product_version 的关系，同一个 promotion_key 在分析分区中的位置可能不同，从而产生不同的 NTILE() 编号。因此，DISTINCT 也可能具有不同的结果：
=> SELECT COUNT(*) FROM
(SELECT DISTINCT SIN(FLOOR(MAX(store.store_sales_fact.promotion_key))),
NTILE(79) OVER(PARTITION BY AVG (RADIANS
(store.store_sales_fact.cost_dollar_amount ))
ORDER BY store.store_sales_fact.product_version)
FROM store.store_sales_fact
GROUP BY store.store_sales_fact.product_version,
store.store_sales_fact.sales_dollar_amount ) AS store;
count
-------
1425
(1 row)
如果将 MAX(promotion_key) 添加到分析 ORDER BY，则结果在任何计算机上都是相同的：
=> SELECT COUNT(*) FROM (SELECT DISTINCT MAX(store.store_sales_fact.promotion_key),
NTILE(79) OVER(PARTITION BY MAX(store.store_sales_fact.cost_dollar_amount)
ORDER BY store.store_sales_fact.product_version,
MAX(store.store_sales_fact.promotion_key))
FROM store.store_sales_fact
GROUP BY store.store_sales_fact.product_version,
store.store_sales_fact.sales_dollar_amount) AS store;
表可以包含集合（数组或集）。数组是允许重复值的元素的有序集合，而集是唯一值的无序集合。
考虑一个订单表，其中包含产品密钥、客户密钥、订单价格和订单日期列，以及一些包含数组。Vertica 中的基本查询结果如下：
=> SELECT * from orders LIMIT 5;
orderkey | custkey | prodkey | orderprices | orderdate
----------+---------+------------------------+-----------------------------+------------
19626 | 91 | ["P1262","P68","P101"] | ["192.59","49.99","137.49"] | 2021-03-14
25646 | 716 | ["P997","P31","P101"] | ["91.39","29.99","147.49"] | 2021-03-14
25647 | 716 | ["P12"] | ["8.99"] | 2021-03-14
19743 | 161 | ["P68","P101"] | ["49.99","137.49"] | 2021-03-15
19888 | 241 | ["P1262","P101"] | ["197.59","142.49"] | 2021-03-15
(5 rows)
如本例所示，数组值以 JSON 格式返回。
集的值也以 JSON 数组格式返回：
=> SELECT custkey,email_addrs FROM customers LIMIT 4;
custkey | email_addrs
---------+------------------------------------------------------------------------
342176 | ["joe.smith@example.com"]
342799 | ["bob@example,com","robert.jones@example.com"]
342845 | ["br92@cs.example.edu"]
342321 | ["789123@example-isp.com","sjohnson@eng.example.com","sara@johnson.example.name"]
您可以访问具有多个索引的嵌套数组（多维数组）的元素，如以下示例所示：
=> SELECT host, pingtimes FROM network_tests;
host | pingtimes
------+-------------------------------------------------------
eng1 | [[24.24,25.27,27.16,24.97], [23.97,25.01,28.12,29.50]]
eng2 | [[27.12,27.91,28.11,26.95], [29.01,28.99,30.11,31.56]]
qa1 | [[23.15,25.11,24.63,23.91], [22.85,22.86,23.91,31.52]]
(3 rows)
=> SELECT pingtimes[0] FROM network_tests;
pingtimes
-------------------------
[24.24,25.27,27.16,24.97]
[27.12,27.91,28.11,26.95]
[23.15,25.11,24.63,23.91]
(3 rows)
=> SELECT pingtimes[0][0] FROM network_tests;
pingtimes
-----------
24.24
27.12
23.15
(3 rows)
Vertica 支持多个函数来操作数组和集。
考虑同一订单表，其中包含在单个订单中购买的所有商品的产品密钥数组。您可以使用 APPLY_COUNT_ELEMENTS 函数来找出每个订单包含的商品数量。该函数标识 prodkey 数组中非 null 元素的数量：
=> SELECT apply_count_elements(prodkey) FROM orders LIMIT 5;
apply_count_elements
--------------------
3
2
2
3
1
(5 rows)
Vertica 还支持集合元素的聚合函数。现在，考虑同一表中的一列，其中包含在单个订单中购买的每件商品的价格数组。您可以使用 APPLY_SUM 函数来查找每个订单的总花费：
=> SELECT apply_sum(orderprices) from orders LIMIT 5;
apply_sum
-----------
380.07
187.48
340.08
268.87
8.99
(5 rows)
大部分数组函数只对一维数组进行操作。要将它们与多维数组一起使用，首先要取消引用一维：
=> SELECT apply_max(pingtimes[0]) FROM network_tests;
apply_max
-----------
27.16
28.11
25.11
(3 rows)
有关完整的函数列表，请参阅集合函数。
您可以在查询中同时包含列名和字面量值。以下示例返回每个订单中商品数量大于三个的订单的产品密钥：
=> SELECT prodkey FROM orders WHERE apply_count_elements(prodkey)>2;
prodkey
------------------------
["P1262","P68","P101"]
["P997","P31","P101"]
(2 rows)
考虑一个较复杂的查询，该查询通过联接两个表 cust 和 orders 来返回客户密钥、姓名、电子邮件、订单密钥和产品密钥，以获取满足总和大于 150 这一条件的订单：
=> SELECT custkey, cust_custname, cust_email, orderkey, prodkey, orderprices from orders
JOIN cust ON custkey = cust_custkey
WHERE apply_sum(orderprices)>150 ;
custkey| cust_custname | cust_email | orderkey | prodkey | orderprices
-------+------------------+---------------------------+--------------+--------------------------------========---+---------------------------
342799 | "Ananya Patel" | "ananyapatel98@gmail.com" | "113-341987" | ["MG-7190","VA-4028","EH-1247","MS-7018"] | [60.00,67.00,22.00,14.99]
342845 | "Molly Benton" | "molly_benton@gmail.com" | "111-952000" | ["ID-2586","IC-9010","MH-2401","JC-1905"] | [22.00,35.00,90.00,12.00]
342989 | "Natasha Abbasi" | "natsabbasi@live.com" | "111-685238" | ["HP-4024"] | [650.00]
342176 | "Jose Martinez" | "jmartinez@hotmail.com" | "113-672238" | ["HP-4768","IC-9010"] | [899.00,60.00]
342845 | "Molly Benton" | "molly_benton@gmail.com" | "113-864153" | ["AE-7064","VA-4028","GW-1808"] | [72.00,99.00,185.00]
(5 rows)
数组可以包含任意组合的数组和结构，如下例所示：
=> CREATE TABLE orders(
orderid INT,
accountid INT,
shipments ARRAY[
ROW(
shipid INT,
address ROW(
street VARCHAR,
city VARCHAR,
zip INT
),
shipdate DATE
)
]
);
一些订单包含多批货物。为了便于阅读，已在以下输出中插入换行符：
=> SELECT * FROM orders;
orderid | accountid | shipments
---------+-----------+---------------------------------------------------------------------------------------------------------------
99123 | 17 | [{"shipid":1,"address":{"street":"911 San Marcos St","city":"Austin","zip":73344},"shipdate":"2020-11-05"},
{"shipid":2,"address":{"street":"100 main St Apt 4B","city":"Pasadena","zip":91001},"shipdate":"2020-11-06"}]
99149 | 139 | [{"shipid":3,"address":{"street":"100 main St Apt 4B","city":"Pasadena","zip":91001},"shipdate":"2020-11-06"}]
99162 | 139 | [{"shipid":4,"address":{"street":"100 main St Apt 4B","city":"Pasadena","zip":91001},"shipdate":"2020-11-04"},
{"shipid":5,"address":{"street":"100 Main St Apt 4A","city":"Pasadena","zip":91001},"shipdate":"2020-11-11"}]
(3 rows)
您可以在查询中同时使用数组索引和结构字段选择：
=> SELECT orderid, shipments[0].shipdate AS ship1, shipments[1].shipdate AS ship2 FROM orders;
orderid | ship1 | ship2
---------+------------+------------
99123 | 2020-11-05 | 2020-11-06
99149 | 2020-11-06 |
99162 | 2020-11-04 | 2020-11-11
(3 rows)
此示例选择特定数组索引。要访问所有条目，请使用 EXPLODE。
某些数据格式具有映射类型，它是一组键/值对。Vertica 不直接支持查询映射，但您可以将映射列定义为结构数组并对其进行查询。在以下示例中，数据中的 "prods" 列是一个映射：
=> CREATE EXTERNAL TABLE orders
(orderkey INT,
custkey INT,
prods ARRAY[ROW(key VARCHAR(10), value DECIMAL(12,2))],
orderdate DATE
) AS COPY FROM '...' PARQUET;
=> SELECT orderkey, prods FROM orders;
orderkey | prods
----------+--------------------------------------------------------------------------------------------------
19626 | [{"key":"P68","value":"49.99"},{"key":"P1262","value":"192.59"},{"key":"P101","value":"137.49"}]
25646 | [{"key":"P997","value":"91.39"},{"key":"P101","value":"147.49"},{"key":"P31","value":"29.99"}]
25647 | [{"key":"P12","value":"8.99"}]
19743 | [{"key":"P68","value":"49.99"},{"key":"P101","value":"137.49"}]
19888 | [{"key":"P1262","value":"197.59"},{"key":"P101","value":"142.49"}]
(5 rows)
您不能在 CREATE TABLE AS SELECT (CTAS) 或视图中使用混合列。此限制适用于整个列或其中的字段选择。
您可以将 比较运算符 用于集合。null 集合排在最后。否则，对集合中的元素逐个比较，直到出现不匹配，然后根据不匹配的元素对集合进行排序。如果所有元素的长度都依次等于较短元素的长度，则首先对较短的元素进行排序。
您可以在查询的 ORDER BY 和 GROUP BY 子句中使用集合。以下示例演示如何按数组列对查询结果进行排序：
=> CREATE TABLE employees (id INT, department VARCHAR(50), grants ARRAY[VARCHAR], grant_values ARRAY[INT]);
=> COPY employees FROM STDIN;
42|Physics|[US-7376,DARPA-1567]|[65000,135000]
36|Physics|[US-7376,DARPA-1567]|[10000,25000]
33|Physics|[US-7376]|[30000]
36|Astronomy|[US-7376,DARPA-1567]|[5000,4000]
\.
=> SELECT * FROM employees ORDER BY grant_values;
id | department | grants | grant_values
----+------------+--------------------------+----------------
36 | Astronomy | ["US-7376","DARPA-1567"] | [5000,4000]
36 | Physics | ["US-7376","DARPA-1567"] | [10000,25000]
33 | Physics | ["US-7376"] | [30000]
42 | Physics | ["US-7376","DARPA-1567"] | [65000,135000]
(4 rows)
以下示例使用 GROUP BY 查询同一个表：
=> CREATE TABLE employees (id INT, department VARCHAR(50), grants ARRAY[VARCHAR], grant_values ARRAY[INT]);
=> COPY employees FROM STDIN;
42|Physics|[US-7376,DARPA-1567]|[65000,135000]
36|Physics|[US-7376,DARPA-1567]|[10000,25000]
33|Physics|[US-7376]|[30000]
36|Astronomy|[US-7376,DARPA-1567]|[5000,4000]
\.
=> SELECT department, grants, SUM(apply_sum(grant_values)) FROM employees GROUP BY grants, department;
department | grants | SUM
------------+--------------------------+--------
Physics | ["US-7376","DARPA-1567"] | 235000
Astronomy | ["US-7376","DARPA-1567"] | 9000
Physics | ["US-7376"] | 30000
(3 rows)
有关 Vertica 如何对集合进行排序的信息，请参阅 ARRAY 参考页面上的“函数和运算符”部分。（SET 参考页面上也提供了同样的信息。）
集合的空语义在大多数方面与普通列一致。有关空处理的更多信息，请参阅 NULL 排序顺序。
当集合为 null 而不是空时，空安全相等运算符 (<=>) 的行为与相等 (=) 不同。将集合严格地与 NULL 进行比较是未定义的。
=> SELECT ARRAY[1,3] = NULL;
?column?
----------
(1 row)
=> SELECT ARRAY[1,3] <=> NULL;
?column?
----------
f
(1 row)
在以下示例中，表中的授予列对于员工 99 为 null。
=> SELECT grants = NULL FROM employees WHERE id=99;
?column?
----------
(1 row)
=> SELECT grants <=> NULL FROM employees WHERE id=99;
?column?
----------
t
(1 row)
空集合不为 null 并且按预期运行。
=> SELECT ARRAY[]::ARRAY[INT] = ARRAY[]::ARRAY[INT];
?column?
----------
t
(1 row)
集合逐个元素进行比较。如果比较依赖于 null 元素，则结果是未知的 (null)，而不是 false。例如，ARRAY[1,2,null]=ARRAY[1,2,null] 和 ARRAY[1,2,null]=ARRAY[1,2,3] 都返回 null，但 ARRAY[1,2,null]=ARRAY[1,4,null] 因为第二个元素不匹配而返回 false。
集合中的越界索引返回 NULL。
=> SELECT prodkey[2] from orders LIMIT 4;
prodkey
---------
"EH-1247"
"MH-2401"
(4 rows)
由于指定的索引大于这些数组的大小，因此四行中的两行（第一行和第四行）的查询结果返回 NULL。
当表达式值的数据类型明确时，会隐式地强制转换，以与预期数据类型相匹配。但是，表达式的数据类型可能不明确。例如，日期可能解释为字符串或时间戳。编写显式强制转换以避免使用默认值：
=> SELECT apply_count_elements(ARRAY['2019-01-20','2019-02-12','2019-03-23']::ARRAY[TIMESTAMP]);
apply_count_elements
--------------------
3
(1 row)
您可以按照与强制转换标量值相同的规则，将一种标量类型的数组或集强制转换为其他（兼容）类型的数组或集。强制转换一个集合会强制转换该集合的每个元素。将数组强制转换为集也会移除任何重复项。
您可以使用作为数组或结构（或二者的组合）的元素来强制转换数组（而不是集）：
=> SELECT shipments::ARRAY[ROW(id INT,addr ROW(VARCHAR,VARCHAR,INT),shipped DATE)]
FROM orders;
shipments
---------------------------------------------------------------------------
[{"id":1,"addr":{"street":"911 San Marcos St","city":"Austin","zip":73344},"shipped":"2020-11-05"},
{"id":2,"addr":{"street":"100 main St Apt 4B","city":"Pasadena","zip":91001},"shipped":"2020-11-06"}]
[{"id":3,"addr":{"street":"100 main St Apt 4B","city":"Pasadena","zip":91001},"shipped":"2020-11-06"}]
[{"id":4,"addr":{"street":"100 main St Apt 4B","city":"Pasadena","zip":91001},"shipped":"2020-11-04"},
{"id":5,"addr":{"street":"100 Main St Apt 4A","city":"Pasadena","zip":91001},"shipped":"2020-11-11"}]
(3 rows)
您可以通过强制转换来更改数组或集的边界。当强制转换为有界原生数组时，会截断太长的输入。强制转换为多维数组时，如果新边界对于数据而言太小，则强制转换失败：
=> SELECT ARRAY[1,2,3]::ARRAY[VARCHAR,2];
array
-----------
["1","2"]
(1 row)
=> SELECT ARRAY[ARRAY[1,2,3],ARRAY[4,5,6]]::ARRAY[ARRAY[VARCHAR,2],2];
ERROR 9227: Output array isn't big enough
DETAIL: Type limit is 4 elements, but value has 6 elements
如果强制转换为有界多维数组，则必须指定所有级别的边界。
必须显式强制转换具有单个 null 元素的数组或集，因为无法推断出任何类型。
有关数据类型强制转换的详细信息，请参阅数据类型强制转换。
您可以使用 EXPLODE 简化对存储在数组中的元素的查询，该函数从表中获取数组列并展开它们。对于每个分割的数组，结果包括两列，一列用于数组元素索引，另一列用于该位置的值。如果该函数分割单个数组，这些列默认命名为 position 和 value。如果该函数分割两个或更多数组，则每个数组的列命名为
pos_column-name 和
val_column-name。
该函数分割参数列表中的前 N 个数组列（N 默认值为 1），并传递所有其他列。
以下示例说明了将 EXPLODE() 与 OVER(PARTITION BEST) 子句一起使用。
考虑一个订单表，其中包含订单键、客户键、产品键、订单价格和电子邮件地址的列，其中一些包含数组。Vertica 中的基本查询结果如下：
=> SELECT orderkey, custkey, prodkey, orderprices, email_addrs FROM orders LIMIT 5;
orderkey | custkey | prodkey | orderprices | email_addrs
------------+---------+-----------------------------------------------+-----------------------------------+----------------------------------------------------------------------------------------------------------------
113-341987 | 342799 | ["MG-7190 ","VA-4028 ","EH-1247 ","MS-7018 "] | ["60.00","67.00","22.00","14.99"] | ["bob@example,com","robert.jones@example.com"]
111-952000 | 342845 | ["ID-2586 ","IC-9010 ","MH-2401 ","JC-1905 "] | ["22.00","35.00",null,"12.00"] | ["br92@cs.example.edu"]
111-345634 | 342536 | ["RS-0731 ","SJ-2021 "] | ["50.00",null] | [null]
113-965086 | 342176 | ["GW-1808 "] | ["108.00"] | ["joe.smith@example.com"]
111-335121 | 342321 | ["TF-3556 "] | ["50.00"] | ["789123@example-isp.com","alexjohnson@example.com","monica@eng.example.com","sara@johnson.example.name",null]
(5 rows)
此示例按升序扩展指定客户的 orderprices 列。custkey 和 email_addrs 列对每个数组元素重复。
=> SELECT EXPLODE(orderprices, custkey, email_addrs) OVER(PARTITION BEST) AS (position, orderprices, custkey, email_addrs)
FROM orders WHERE custkey='342845' ORDER BY orderprices;
position | orderprices | custkey | email_addrs
----------+-------------+---------+------------------------------
2 | | 342845 | ["br92@cs.example.edu",null]
3 | 12.00 | 342845 | ["br92@cs.example.edu",null]
0 | 22.00 | 342845 | ["br92@cs.example.edu",null]
1 | 35.00 | 342845 | ["br92@cs.example.edu",null]
(4 rows)
展开包含空值的列时，null 值显示为空。
您可以通过指定 explode_count 参数来展开多个列。
=> SELECT EXPLODE(orderkey, prodkey, orderprices USING PARAMETERS explode_count=2)
OVER(PARTITION BEST)
AS (orderkey,pk_idx,pk_val,ord_idx,ord_val)
FROM orders
WHERE orderkey='113-341987';
orderkey | pk_idx | pk_val | ord_idx | ord_val
------------+--------+----------+---------+---------
113-341987 | 0 | MG-7190 | 0 | 60.00
113-341987 | 0 | MG-7190 | 1 | 67.00
113-341987 | 0 | MG-7190 | 2 | 22.00
113-341987 | 0 | MG-7190 | 3 | 14.99
113-341987 | 1 | VA-4028 | 0 | 60.00
113-341987 | 1 | VA-4028 | 1 | 67.00
113-341987 | 1 | VA-4028 | 2 | 22.00
113-341987 | 1 | VA-4028 | 3 | 14.99
113-341987 | 2 | EH-1247 | 0 | 60.00
113-341987 | 2 | EH-1247 | 1 | 67.00
113-341987 | 2 | EH-1247 | 2 | 22.00
113-341987 | 2 | EH-1247 | 3 | 14.99
113-341987 | 3 | MS-7018 | 0 | 60.00
113-341987 | 3 | MS-7018 | 1 | 67.00
113-341987 | 3 | MS-7018 | 2 | 22.00
113-341987 | 3 | MS-7018 | 3 | 14.99
(16 rows)
以下示例使用多维数组：
=> SELECT name, pingtimes FROM network_tests;
name | pingtimes
------+-------------------------------------------------------
eng1 | [[24.24,25.27,27.16,24.97],[23.97,25.01,28.12,29.5]]
eng2 | [[27.12,27.91,28.11,26.95],[29.01,28.99,30.11,31.56]]
qa1 | [[23.15,25.11,24.63,23.91],[22.85,22.86,23.91,31.52]]
(3 rows)
=> SELECT EXPLODE(name, pingtimes USING PARAMETERS explode_count=1) OVER()
FROM network_tests;
name | position | value
------+----------+---------------------------
eng1 | 0 | [24.24,25.27,27.16,24.97]
eng1 | 1 | [23.97,25.01,28.12,29.5]
eng2 | 0 | [27.12,27.91,28.11,26.95]
eng2 | 1 | [29.01,28.99,30.11,31.56]
qa1 | 0 | [23.15,25.11,24.63,23.91]
qa1 | 1 | [22.85,22.86,23.91,31.52]
(6 rows)
有关在 Vertica 中实施这些数据类型的详细信息，请参阅 ARRAY 和 SET。
IMPLODE 函数是 EXPLODE 的反函数：它接受一列，并生成一个包含该列值的数组。与 GROUP BY 结合使用，可用于反转分割操作。
您可以同时使用 EXPLODE 和 IMPLODE 来筛选数组值。例如，在一组价格为数组值的订单中，您可能只想查询价格低于某个阈值的订单。请考虑下表：
=> SELECT * FROM orders;
key | prices
-----+-------------------
567 | [27.99,18.99]
789 | [108.0]
345 | [14.99,35.99]
123 | [60.0,67.0,14.99]
(4 rows)
您可以使用 EXPLODE 来展开数组。为清楚起见，此示例创建一个新表来保存结果。更典型的做法是，您在子查询中使用 EXPLODE 和 IMPLODE 而不是创建中间表。
=> CREATE TABLE exploded AS
SELECT EXPLODE(prices,key) OVER (PARTITION BEST)
AS (position, itemprice, itemkey) FROM orders;
=> SELECT * FROM exploded;
position | itemprice | itemkey
----------+-----------+---------
0 | 108 | 789
1 | 35.99 | 345
0 | 14.99 | 345
0 | 27.99 | 567
0 | 60 | 123
1 | 18.99 | 567
1 | 67 | 123
2 | 14.99 | 123
(8 rows)
您现在可以筛选已分割的价格：
=> CREATE TABLE filtered AS
SELECT position, itemprice, itemkey FROM orders WHERE itemprice < 50.00;
=> SELECT * FROM filtered;
position | itemprice | itemkey
----------+-----------+---------
0 | 14.99 | 345
0 | 27.99 | 567
1 | 18.99 | 567
1 | 35.99 | 345
2 | 14.99 | 123
(5 rows)
最后，您可以使用 IMPLODE 来重构数组：
=> SELECT itemkey AS key, IMPLODE(itemprice) AS prices
FROM filtered GROUP BY itemkey ORDER BY itemkey;
key | prices
-----+-------------------
123 | ["14.99"]
345 | ["35.99","14.99"]
567 | ["27.99","18.99"]
(3 rows)
您可以通过包括 WITHIN GROUP ORDER BY 子句来修改此查询，该子句指定如何对每个组中的数组元素进行排序：
=> SELECT itemkey AS key, IMPLODE(itemprice) WITHIN GROUP (ORDER BY itemprice) AS prices
FROM filtered GROUP BY itemkey ORDER BY itemkey;
key | prices
-----+-------------------
123 | ["14.99"]
345 | ["14.99","35.99"]
567 | ["18.99","27.99"]
(3 rows)
如果 IMPLODE 要返回的数组对于该列来说太大，则该函数将返回一个错误。为避免这种情况，您可以将 allow_truncate 参数设置为在结果中省略一些元素。截断永远不会应用于单个元素；例如，该函数不会缩短字符串。
表可以包含 ROW 数据类型的列。ROW（有时称为结构）是一组类型化的属性值对。
考虑一个包含 name、address 和 ID 列的 customers 表。地址是一个 ROW，其中包含地址元素的字段（街道、城市和邮政编码）。如本例所示，ROW 值以 JSON 格式返回：
=> SELECT * FROM customers ORDER BY accountID;
name | address | accountID
--------------------+--------------------------------------------------------------------+-----------
Missy Cooper | {"street":"911 San Marcos St","city":"Austin","zipcode":73344} | 17
Sheldon Cooper | {"street":"100 Main St Apt 4B","city":"Pasadena","zipcode":91001} | 139
Leonard Hofstadter | {"street":"100 Main St Apt 4A","city":"Pasadena","zipcode":91001} | 142
Leslie Winkle | {"street":"23 Fifth Ave Apt 8C","city":"Pasadena","zipcode":91001} | 198
Raj Koothrappali | {"street":null,"city":"Pasadena","zipcode":91001} | 294
Stuart Bloom | | 482
(6 rows)
大部分的值都强制转换为 UTF-8 字符串，如此处的 street 和 city 所示。整数和布尔值强制转换为 JSON 数字，因此不被引用。
使用点表示法 (column.field) 访问各个字段：
=> SELECT address.city FROM customers;
city
----------
Pasadena
Pasadena
Pasadena
Pasadena
Austin
(6 rows)
在以下示例中，customers 表中的联系信息有一个 email 字段，该字段是地址数组：
=> SELECT name, contact.email FROM customers;
name | email
--------------------+---------------------------------------------
Missy Cooper | ["missy@mit.edu","mcooper@cern.gov"]
Sheldon Cooper | ["shelly@meemaw.name","cooper@caltech.edu"]
Leonard Hofstadter | ["hofstadter@caltech.edu"]
Leslie Winkle | []
Raj Koothrappali | ["raj@available.com"]
Stuart Bloom |
(6 rows)
您可以使用 ROW 列或特定字段来限制查询，如下例所示：
=> SELECT address FROM customers WHERE address.city ='Pasadena';
address
--------------------------------------------------------------------
{"street":"100 Main St Apt 4B","city":"Pasadena","zipcode":91001}
{"street":"100 Main St Apt 4A","city":"Pasadena","zipcode":91001}
{"street":"23 Fifth Ave Apt 8C","city":"Pasadena","zipcode":91001}
{"street":null,"city":"Pasadena","zipcode":91001}
(4 rows)
您可以使用 ROW 语法来指定字面量值，例如以下示例中 WHERE 子句中的地址：
=> SELECT name,address FROM customers
WHERE address = ROW('100 Main St Apt 4A','Pasadena',91001);
name | address
--------------------+-------------------------------------------------------------------
Leonard Hofstadter | {"street":"100 Main St Apt 4A","city":"Pasadena","zipcode":91001}
(1 row)
您可以像从任何其他列中一样联接字段值：
=> SELECT accountID,department from customers JOIN employees
ON customers.name=employees.personal.name;
accountID | department
-----------+------------
139 | Physics
142 | Physics
294 | Astronomy
您可以联接完整的结构。以下示例联接 employees 和 customers 表中的地址：
=> SELECT employees.personal.name,customers.accountID FROM employees
JOIN customers ON employees.personal.address=customers.address;
name | accountID
--------------------+-----------
Sheldon Cooper | 139
Leonard Hofstadter | 142
(2 rows)
您可以强制转换结构，且可选择指定新的字段名称：
=> SELECT contact::ROW(str VARCHAR, city VARCHAR, zip VARCHAR, email ARRAY[VARCHAR,
20]) FROM customers;
contact
--------------------------------------------------------------------------------
----------------------------------
{"str":"911 San Marcos St","city":"Austin","zip":"73344","email":["missy@mit.ed
u","mcooper@cern.gov"]}
{"str":"100 Main St Apt 4B","city":"Pasadena","zip":"91001","email":["shelly@me
emaw.name","cooper@caltech.edu"]}
{"str":"100 Main St Apt 4A","city":"Pasadena","zip":"91001","email":["hofstadte
r@caltech.edu"]}
{"str":"23 Fifth Ave Apt 8C","city":"Pasadena","zip":"91001","email":[]}
{"str":null,"city":"Pasadena","zip":"91001","email":["raj@available.com"]}
(6 rows)
您可以在视图和子查询中使用结构，如下例所示：
=> CREATE VIEW neighbors (num_neighbors, area(city, zipcode))
AS SELECT count(*), ROW(address.city, address.zipcode)
FROM customers GROUP BY address.city, address.zipcode;
CREATE VIEW
=> SELECT employees.personal.name, neighbors.area FROM neighbors, employees
WHERE employees.personal.address.zipcode=neighbors.area.zipcode AND neighbors.nu
m_neighbors > 1;
name | area
--------------------+-------------------------------------
Sheldon Cooper | {"city":"Pasadena","zipcode":91001}
Leonard Hofstadter | {"city":"Pasadena","zipcode":91001}
(2 rows)
如果引用不明确，Vertica 会优先选择列名而不是字段名。
您可以对 ROW 列使用许多运算符和谓词，包括 JOIN、GROUP BY、ORDER BY、IS [NOT] NULL 以及可 null 筛选器中的比较操作。某些运算符在逻辑上不适用于结构化数据并且不受支持。有关完整列表，请参阅 ROW 参考页面。
子查询是一种嵌套在其他 SELECT 语句中的 SELECT 语句。嵌套子查询通常称为查询内语句，而包含查询通常称为查询语句或外查询块。子查询返回外查询用作条件的数据，以确定需要检索哪些数据。您可以创建的嵌套子查询的数量没有限制。
与任何查询一样，子查询返回（单列单记录、单列多记录或多列多记录）表中的记录。查询可以是非相关或相关查询。您甚至可以使用它们基于存储在其他数据库表中的值来更新或删除表中的记录。
子查询需要用作搜索条件才能过滤结果。它们指定了从包含查询的 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 )
}
AND 和 OR 逻辑运算符会组合两个条件。当由 AND 关键字联接的两个条件都有匹配时，AND 评估为 TRUE，当由 OR 关键字联接的一个条件有匹配时，则 OR 评估为 TRUE。
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
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;
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);
返回单值（与 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
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);
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 谓词。
您通常只对返回一行的子查询使用比较运算符（=、>、< 等）。使用 ANY 和 ALL 运算符，可在返回多个行的子查询中进行比较。
这些子查询采用以下格式：
expression comparison-operator { ANY | ALL } (subquery)
ANY 和 ALL 评估子查询返回的任何或所有值是否与左侧表达式匹配。
可以使用以下运算符代替 ANY 或 ALL：
以下示例使用以下表和数据：
当在子查询中检索的任何值与左侧表达式的值相匹配时，使用 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 关键字的子查询返回 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)
如果列没有标记为 NOT NULL，则 Vertica 支持多列 <> ALL 子查询。如果任何列包含 NULL 值，Vertica 将返回运行时错误。
如果任何列值为 NULL，则 Vertica 不支持嵌套在另一个表达式中的 = ANY 子查询。
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)
尽管无法使一个单值与一个值集相等，但您可以查看单值是否已在此值集中。对多记录单列子查询使用 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
子查询可以在包含查询的 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 值；子查询自身可以拥有多个行。大多数查询可以为相关或非相关查询。
ORDER BY 和 GROUP BY 子句中的子查询受支持，例如，以下语句表示按第一列进行排序，该列是 select-list 子查询：
=> SELECT (SELECT MAX(x) FROM t2 WHERE y=t1.b) FROM t1 ORDER BY 1;
WITH 子句是较大的主要查询中的伴随查询。Vertica 可以通过两种方式评估 WITH 子句：
内联扩展（默认）：当主要查询每次引用每个 WITH 子句时，Vertica 才会对它进行评估。
实体化：Vertica 对每个 WITH 子句进行一次评估，将结果存储在临时表中，然后在查询需要时引用此表。
有关语法选项和要求的详细信息，请参阅 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 子句后，评估主要查询。
实体化启用之后，Vertica 会对每个 WITH 子句进行一次评估，将结果存储在临时表中，然后在查询需要时引用此表。主要查询执行完成之后，Vertica 会将临时表删除。
如果 WITH 子句非常复杂，例如当 WITH 子句包含 JOIN 和 GROUP BY 子句，并且在主要查询中多次被引用时，实体化可帮助提高性能。
如果实体化已启用，WITH 语句会自动提交用户事务。即使将 EXPLAIN 与 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)
...
默认情况下，当重用 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 子句，都会使用表中存储的结果。
查询执行完成之后，临时表会删除。
包含 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 返回两个级别的数据：
默认情况下，实体化处于禁用状态。在这种情况下，Vertica 将 WITH RECURSIVE 查询重写为子查询，其数量与所需的递归级别一样多。
如果递归非常深，则大量查询重写可能会产生相当大的开销，从而对性能产生不利影响并耗尽系统资源。在这种情况下，请考虑使用配置参数 WithClauseMaterialization 或提示 ENABLE_WITH_CLAUSE_MATERIALIZATION 来启用实体化。在任何一种情况下，来自所有递归级别的中间结果集都将写入本地临时表。当递归完成时，所有临时表中的中间结果都进行编译并传递给主要查询。
子查询可以分为以下两种类型：
非相关（简单）子查询获取结果不需要依赖其包含（外）语句。
相关子查询需要来自其外查询的值才能执行。
非相关子查询的执行不需要依赖外查询。子查询首先执行，然后将结果传递给外查询，例如：
=> 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。
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;
您可以在 UPDATE 和 DELETE 语句中嵌套子查询。
您可以通过在 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 语句中嵌套子查询，根据其他表中的值删除一个表中的记录。
例如，您想从之前用于更新 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)
此主题显示了一些可以编写的子查询。示例使用 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 子句与 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)
以下限制适用于 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);
查询可以合并多个表中的记录或同一个表的多个实例。合并一个或多个表中的记录的查询称为联接。SELECT 语句和子查询中允许联接。
Vertica 支持以下联接类型：
内联接（包括自然联接、交叉联接）
左外联接、右外联接和全外联接
等式和范围联接谓词的优化
Vertica 不支持嵌套循环联接。
Vertica 的查询优化器使用哈希联接或合并联接算法来实施联接。有关详细信息，请参阅哈希联接与合并联接。
Vertica 支持用于联接表的 ANSI SQL-92 标准，如下所示：
table‑reference [join-type] JOIN table-reference [ ON join-predicate ]
其中，join-type 可以是以下类型之一：
例如：
=> SELECT * FROM T1 INNER JOIN T2 ON T1.id = T2.id;
ON join-predicate 子句无效，而对于所有其他联接类型，该子句是必需的。
Vertica 还支持两种较旧的联接语法约定：
INNER JOIN 等效于在 WHERE 子句中指定其联接谓词的查询。例如，此示例和上一个示例返回相同的结果。它们都分别在 T1.id 和 T2.id 列上指定 T1 和 T2 表之间的内部联接。
=> SELECT * FROM T1, T2 WHERE T1.id = T2.id;
可以使用 JOIN USING 子句在同名列上联接两个表。例如：
=> SELECT * FROM T1 JOIN T2 USING(id);
默认情况下，由 JOIN USING 指定的联接始终是内联接。
Vertica 建议您使用 SQL-92 联接语法，原因如下：
SQL-92 外联接语法可跨数据库移植；旧语法不能在数据库之间保持一致。
SQL-92 语法可更好地控制是在外联接期间还是之后评估谓词。如果使用旧语法，这同样无法在数据库之间保持一致。
SQL-92 语法消除了在通过外联接联接两个以上的表这种情况下，联接评估顺序的不确定性。
如果不使用 SQL-92 语法，联接条件（联接过程中评估的谓词）很难与筛选条件（联接之后评估的谓词）区分开来，而且在某些情况下根本无法表达。使用 SQL-92 时，联接条件和筛选条件会分为两个不同的子句，即 ON 子句和 WHERE 子句，这样查询更易于理解。
ON 子句 包含关系运算符（例如 <、<=、>、>=、<>、=、<=>）或用于指定合并左输入关系和右输入关系中哪些记录（例如通过匹配外键与主键）的其他谓词。ON 可用于内联接、左外联接、右外联接和全外联接。交叉联接和联合联接不能使用 ON 子句。
内联接返回经 ON 子句评估为 TRUE 的左关系和右关系中的所有行对。在左联接中，联接中左关系的所有行出现在结果中；左关系与右关系中不匹配的行也会出现在结果中，但包含在右关系中获取的任何列中的 NULL。同样地，右联接会保留右关系的所有行，而全联接保留左右两个关系的所有行。
WHERE 子句 在联接执行之后进行评估。它会筛选 FROM 子句返回的记录，清除掉不符合 WHERE 子句条件的所有记录。
Vertica 自动将外联接转换为内联接（如果合适），允许优化器在更广泛的查询计划集中选择计划，从而实现更好的性能。
内联接基于联接谓词将两个表中的记录组合在一起，并要求第一个表中每个记录在第二个表中都具有匹配的记录。因此，内联接只会返回两个已联接表中符合联接条件的记录。不包含匹配项的记录将从结果集中排除。
内联接采用以下格式：
SELECT column‑list FROM left-join-table
[INNER] JOIN right-join-table ON join-predicate
如果您省略 INNER 关键字，则 Vertica 假定内联接。内联接是可交换的和关联的。您可以按任何顺序指定表而不更改结果。
以下示例指定表 store.store_dimension 和 public.employee_dimension 之间的内联接，其记录分别在 store_region 和 employee_region 列中具有匹配值。
=> SELECT s.store_region, SUM(e.vacation_days) TotalVacationDays
FROM public.employee_dimension e
JOIN store.store_dimension s ON s.store_region=e.employee_region
GROUP BY s.store_region ORDER BY TotalVacationDays;
这种联接也可以表示如下：
=> SELECT s.store_region, SUM(e.vacation_days) TotalVacationDays
FROM public.employee_dimension e, store.store_dimension s
WHERE s.store_region=e.employee_region
GROUP BY s.store_region ORDER BY TotalVacationDays;
两个查询返回相同的结果集：
store_region | TotalVacationDays
--------------+-------------------
NorthWest | 23280
SouthWest | 367250
MidWest | 925938
South | 1280468
East | 1952854
West | 2849976
(6 rows)
如果联接的内表 store.store_dimension 有任何行的 store_region 值与表 public.employee_dimension 中的 employee_region 值不匹配，则从结果集中排除这些行。要包含该行，您可以指定外联接。
Vertica 支持含有匹配列值和非匹配列值的任意联接表达式。例如：
SELECT * FROM fact JOIN dim ON fact.x = dim.x;
SELECT * FROM fact JOIN dim ON fact.x > dim.y;
SELECT * FROM fact JOIN dim ON fact.x <= dim.y;
SELECT * FROM fact JOIN dim ON fact.x <> dim.y;
SELECT * FROM fact JOIN dim ON fact.x <=> dim.y;
= 和 <=> 运算符的运行速度通常是最快的。
等联接以等式（匹配列值）为基础。此等式以等于号 (=) 表示，它在使用 SQL-92 语法的 ON 子句或使用更旧的联接语法的 WHERE 子句中用作比较运算符。
下文中第一个示例使用 SQL-92 和 ON 子句将线上销售额表与使用呼叫中心键的呼叫中心表联接在一起，然后查询返回等于 156 的销售日期键：
=> SELECT sale_date_key, cc_open_date FROM online_sales.online_sales_fact
INNER JOIN online_sales.call_center_dimension
ON (online_sales.online_sales_fact.call_center_key =
online_sales.call_center_dimension.call_center_key
AND sale_date_key = 156);
sale_date_key | cc_open_date
---------------+--------------
156 | 2005-08-12
(1 row)
第二个示例使用更旧的联接语法和 WHERE 子句联接上述表，获得了相同的结果：
=> SELECT sale_date_key, cc_open_date
FROM online_sales.online_sales_fact, online_sales.call_center_dimension
WHERE online_sales.online_sales_fact.call_center_key =
online_sales.call_center_dimension.call_center_key
AND sale_date_key = 156;
sale_date_key | cc_open_date
---------------+--------------
156 | 2005-08-12
(1 row)
Vertica 还允许使用包含复合（多列）主键和外键的表。例如，要创建一对包含多列键的表：
=> CREATE TABLE dimension(pk1 INTEGER NOT NULL, pk2 INTEGER NOT NULL);=> ALTER TABLE dimension ADD PRIMARY KEY (pk1, pk2);
=> CREATE TABLE fact (fk1 INTEGER NOT NULL, fk2 INTEGER NOT NULL);
=> ALTER TABLE fact ADD FOREIGN KEY (fk1, fk2) REFERENCES dimension (pk1, pk2);
要联接使用复合键的表，您必须用 Boolean AND 运算符连接两个联接谓词。例如：
=> SELECT * FROM fact f JOIN dimension d ON f.fk1 = d.pk1 AND f.fk2 = d.pk2;
您可以通过含有 NULL=NULL 联接的 <=> 运算符的表达式编写查询。
=> SELECT * FROM fact JOIN dim ON fact.x <=> dim.y;
<=> 运算符与 = 运算符一样，执行等式比较，但它在两个操作数都为 NULL 时返回 true（而不是 NULL），并在一个操作数为 NULL 时返回 false（而不是 NULL）。
=> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
?column? | ?column? | ?column?
----------+----------+----------
t | t | f
(1 row)
比较 <=> 运算符和 = 运算符：
=> SELECT 1 = 1, NULL = NULL, 1 = NULL;
?column? | ?column? | ?column?
----------+----------+----------
t | |
(1 row)
NULL=NULL 联接不是最佳选择，因为 PK/FK 列通常定义为 NOT NULL。
编写联接时，它可帮助您提前知道哪些列包含 NULL 值。例如，员工的雇佣日期就不是一个好选择，因为雇佣日期不可能被忽略。但是，如果一些员工按小时领工资，而一些员工按薪水领工资，这种情况下，可以使用每小时工资列。如果您不确定给定表中的列值，而且希望检查一下，请键入以下命令：
=> SELECT COUNT(*) FROM tablename WHERE columnname IS NULL;
自然联接只是一种包含隐式联接谓词的联接。自然联接可以是内联接、左外联接、右外联接或全外联接，它采用以下格式：
SELECT column‑list FROM left-join-table
NATURAL [ INNER | LEFT OUTER | RIGHT OUTER | FULL OUTER ] JOIN right-join-table
默认情况下，自然联接为自然内联接；但自然联接也可以为自然左/右/全外联接。内联接和自然联接的主要区别在于，内联接具有显式联接条件，而自然联接的条件的形成方式是，对具有相同名称和兼容数据类型的表中所有列对进行匹配，使自然联接变为等联接，因为联接条件在普通的列之间是相等的。（如数据类型不兼容，Vertica 将返回错误。）
当 T2 列 val 大于 5 时，以下查询是 T1 表和 T2 表之间的一个简单的自然联接：
=> SELECT * FROM T1 NATURAL JOIN T2 WHERE T2.val > 5;
store_sales_fact 表和 product_dimension 表有两列共享相同的名称和数据类型：product_key 和 product_version。以下示例在这两个表的共享列处创建自然联接：
=> SELECT product_description, sales_quantity FROM store.store_sales_fact
NATURAL JOIN public.product_dimension;
以下三个查询返回相同的结果，分别表示为基本查询、内联接和自然联接。仅当 store_sales_fact 表和 store_dimension 表中的公共属性为 store_key 时，表的表达式才等效。如果两个表都具有名为 store_key 的列，则自然联接也会具有 store_sales_fact.store_key = store_dimension.store_key 联接条件。由于所有三个实例的结果都相同，因此它们仅显示在第一个（基本）查询中：
=> SELECT store_name FROM store.store_sales_fact, store.store_dimension
WHERE store.store_sales_fact.store_key = store.store_dimension.store_key
AND store.store_dimension.store_state = 'MA' ORDER BY store_name;
store_name
------------
Store11
Store128
Store178
Store66
Store8
Store90
(6 rows)
作为内联接编写的查询：
=> SELECT store_name FROM store.store_sales_fact
INNER JOIN store.store_dimension
ON (store.store_sales_fact.store_key = store.store_dimension.store_key)
WHERE store.store_dimension.store_state = 'MA' ORDER BY store_name;
对于自然联接，通过对由相同的列名称联接的两个表中所有列进行比较，隐式显示联接谓词。结果集对表示一对命名相同的列仅保留一列。
=> SELECT store_name FROM store.store_sales_fact
NATURAL JOIN store.store_dimension
WHERE store.store_dimension.store_state = 'MA' ORDER BY store_name;
交叉联接是一种编写起来最简单的联接，但运行速度通常不是最快的，因为它们包含两个表中记录的所有可能组合。交叉联接不包含任何联接条件，它会返回笛卡尔积，其中结果集中的行数等于第一个表中的行数与第二个表中的行数之乘积。
以下查询返回了促销表和商店销售表的所有可能组合：
=> SELECT * FROM promotion_dimension CROSS JOIN store.store_sales_fact;
由于此示例返回了超过 6 亿个记录，因此，许多交叉联接的结果非常大，不易管理。但是，交叉联接非常有用，例如当您想返回一个单行结果集时。
通过使用 WHERE 子句联接谓词过滤掉交叉联接中不需要的记录：
=> SELECT * FROM promotion_dimension p CROSS JOIN store.store_sales_fact f
WHERE p.promotion_key LIKE f.promotion_key;
Vertica 建议您不要编写隐式交叉联接（FROM 子句中以逗号分隔的表）。这些查询可能会意外忽略了某个联接谓词。
以下查询隐式交叉联接 promotion_dimension 和 store.store_sales_fact 表：
=> SELECT * FROM promotion_dimension, store.store_sales_fact;
更好的做法是明确表达此交叉联接，如下所示：
=> SELECT * FROM promotion_dimension CROSS JOIN store.store_sales_fact;
以下示例创建了两个小表格及其超投影，然后对这两个表运行交叉联接：
=> CREATE TABLE employee(employee_id INT, employee_fname VARCHAR(50));
=> CREATE TABLE department(dept_id INT, dept_name VARCHAR(50));
=> INSERT INTO employee VALUES (1, 'Andrew');
=> INSERT INTO employee VALUES (2, 'Priya');
=> INSERT INTO employee VALUES (3, 'Michelle');
=> INSERT INTO department VALUES (1, 'Engineering');
=> INSERT INTO department VALUES (2, 'QA');
=> SELECT * FROM employee CROSS JOIN department;
在结果集中，交叉联接检索了第一个表中的记录，然后为第二个表中每个行创建一个新行。接着，它对第一个表中的下一个记录重复此操作，依此类推。
employee_id | employee_name | dept_id | dept_name
-------------+---------------+---------+-----------
1 | Andrew | 1 | Engineering
2 | Priya | 1 | Engineering
3 | Michelle | 1 | Engineering
1 | Andrew | 2 | QA
2 | Priya | 2 | QA
3 | Michelle | 2 | QA
(6 rows)
外联接扩展了内联接的功能。通过外联接，您可以保留一个或两个表中在非保留表中没有匹配行的行。外联接采用以下格式：
SELECT column‑list FROM left-join-table
[ LEFT | RIGHT | FULL ] OUTER JOIN right-join-table ON join-predicate
OUTER 关键词不会影响左联接和右联接的结果。LEFT OUTER JOIN和 LEFT JOIN 执行相同的操作并返回相同的结果。
左外联接返回左联接（保留）表 T1 的完整记录集以及右联接（非保留）表 T2 中的匹配记录（如适用）。如果 Vertica 未找到匹配项，它会使用 null 值扩展右侧列 (T2)。
=> SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.x = T2.x;
要排除 T2 中不匹配的值，请编写相同的左外联接，但使用 WHERE 子句过滤掉右表中不需要的记录：
=> SELECT * FROM T1 LEFT OUTER JOIN T2
ON T1.x = T2.x WHERE T2.x IS NOT NULL;
以下示例使用左外联接扩充含有不完整电话号码维度的电话呼叫详细记录。然后，它过滤掉已知的不是来自马萨诸塞州的结果：
=> SELECT COUNT(*) FROM calls LEFT OUTER JOIN numbers
ON calls.to_phone = numbers.phone WHERE NVL(numbers.state, '') <> 'MA';
右外联接返回右联接（保留）表的完整记录集以及左联接（非保留）表中的匹配值。如果 Vertica 从左联接表 (T1) 中未找到匹配记录，则对于 T1 中没有匹配值的任何记录，将在 T1 列中显示 NULL 值。因此，右联接与左联接相似，只是用于联接的表的顺序相反而已。
=> SELECT * FROM T1 RIGHT OUTER JOIN T2 ON T1.x = T2.x;
上述查询等同于以下查询，其中 T1 RIGHT OUTER JOIN T2 = T2 LEFT OUTER JOIN T1。
=> SELECT * FROM T2 LEFT OUTER JOIN T1 ON T2.x = T1.x;
以下示例会标识 没有 下单的客户：
=> SELECT customers.customer_id FROM orders RIGHT OUTER JOIN customers
ON orders.customer_id = customers.customer_id
GROUP BY customers.customer_id HAVING COUNT(orders.customer_id) = 0;
全外联接返回左右两个外联接的结果。联接表包含两个表的所有记录，包括联接任何一侧中的 NULL（缺少匹配）。它非常有用，例如，如果您想查看哪些员工已分配到某个特定部门以及已有一名员工的所有部门，但是您还想查看哪些员工没有分配到某个特定部门以及没有员工的所有部门：
=> SELECT employee_last_name, hire_date FROM employee_dimension emp
FULL OUTER JOIN department dept ON emp.employee_key = dept.department_key;
Vertica 还支持以下联接，即外部（保留）表或子查询在一个以上节点中进行复制，而内部（非保留）表或子查询跨多个节点进行分段。例如，在以下查询中，事实表（大多数情况下已分段）出现在联接的非保留表中，这是允许的：
=> SELECT sales_dollar_amount, transaction_type, customer_name
FROM store.store_sales_fact f RIGHT JOIN customer_dimension d
ON f.customer_key = d.customer_key;
sales_dollar_amount | transaction_type | customer_name
---------------------+------------------+---------------
252 | purchase | Inistar
363 | purchase | Inistar
510 | purchase | Inistar
-276 | return | Foodcorp
252 | purchase | Foodcorp
195 | purchase | Foodcorp
290 | purchase | Foodcorp
222 | purchase | Foodcorp
| | Foodgen
| | Goldcare
(10 rows)
默认情况下，优化器使用自己的内部逻辑来确定将一个表作为内部输入还是外部输入联接到另一个表。有时，优化器可能选择将更大的表作为联接的内部输入。但这样做会导致性能和并发问题。
如果配置参数 EnableForceOuter 设置为 1，则您可以通过
ALTER TABLE..FORCE OUTER 控制特定表的联接输入。FORCE OUTER 选项会在
TABLES 系统表中修改表的 force_outer 设置。实施联接时，Vertica 会比较参与联接的表的 force_outer 设置：
如果表设置不同，Vertica 会使用它们来设置联接输入：
相比其他表具有较低 force_outer 设置的表会作为内部输入联接到这些表。
相比其他表具有较高 force_outer 设置的表会作为外部输入联接到这些表。
如果所有表设置都相同，Vertica 会忽略它们，然后自行决定如何构建联接。
对于所有新定义的表，force_outer 列最初都设置为 5。您可以使用
ALTER TABLE..FORCE OUTER 将 force_outer 重置为等于或大于 0 的值。例如，您可以将 abc 和 xyz 表的 force_outer 设置分别改为 3 和 8：
=> ALTER TABLE abc FORCE OUTER 3;
=> ALTER TABLE xyz FORCE OUTER 8;
根据这些设置，优化器会将 abc 作为内部输入联接到 force_outer 值大于 3 的任何表。优化器会将 xyz 作为外部输入联接到 force_outer 值小于 8 的任何表。
直接查询投影时，它会继承其锚表的 force_outer 设置。然后查询会在联接到另一个投影后使用此设置。
配置参数 EnableForceOuter 决定了 Vertica 是否使用表的 force_outer 值来实施联接。默认情况下，此参数设置为 0，并且强制联接输入处于禁用状态。您可以通过
ALTER SESSION 和
ALTER DATABASE 分别在会话和数据库范围中启用强制联接输入：
=> ALTER SESSION SET EnableForceOuter = { 0 | 1 };
=> ALTER DATABASE db-name SET EnableForceOuter = { 0 | 1 };
如果 EnableForceOuter 设置为 0，则 ALTER TABLE..FORCE OUTER 语句会返回以下警告：
WARNING 0: Set configuration parameter EnableForceOuter for the current session or the database in order to use
force_outer value
EXPLAIN 生成的查询计划会指示 EnableForceOuter 配置参数是否已启用。联接查询可能会包含 force_outer 设置小于或大于默认值 5 的表。在这种情况下，查询计划会包含相关联接查询的 Force outer level 字段。
例如，以下查询联接了 store.store_sales 表和 public.products 表，但这两个表具有相同的 force_outer 设置 (5)。EnableForceOuter已启用，如生成的查询计划中所示：
=> EXPLAIN SELECT s.store_key, p.product_description, s.sales_quantity, s.sale_date
FROM store.store_sales s JOIN public.products p ON s.product_key=p.product_key
WHERE s.sale_date='2014-12-01' ORDER BY s.store_key, s.sale_date;
EnableForceOuter is on
Access Path:
+-SORT [Cost: 7K, Rows: 100K (NO STATISTICS)] (PATH ID: 1)
| Order: sales.store_key ASC, sales.sale_date ASC
| Execute on: All Nodes
| +---> JOIN HASH [Cost: 5K, Rows: 100K (NO STATISTICS)] (PATH ID: 2) Outer (BROADCAST)(LOCAL ROUND ROBIN)
| | Join Cond: (sales.product_key = products.product_key)
| | Execute on: All Nodes
| | +-- Outer -> STORAGE ACCESS for sales [Cost: 2K, Rows: 100K (NO STATISTICS)] (PATH ID: 3)
| | | Projection: store.store_sales_b0
| | | Materialize: sales.sale_date, sales.store_key, sales.product_key, sales.sales_quantity
| | | Filter: (sales.sale_date = '2014-12-01'::date)
| | | Execute on: All Nodes
| | +-- Inner -> STORAGE ACCESS for products [Cost: 177, Rows: 60K (NO STATISTICS)] (PATH ID: 4)
| | | Projection: public.products_b0
| | | Materialize: products.product_key, products.product_description
| | | Execute on: All Nodes
以下 ALTER TABLE 语句将 public.products 的 force_outer 设置重置为 1：
=> ALTER TABLE public.products FORCE OUTER 1;
ALTER TABLE
此时，为上述联接重新生成的查询包含 Force outer level 字段，并将 public.products 指定为内部输入：
=> EXPLAIN SELECT s.store_key, p.product_description, s.sales_quantity, s.sale_date
FROM store.store_sales s JOIN public.products p ON s.product_key=p.product_key
WHERE s.sale_date='2014-12-01' ORDER BY s.store_key, s.sale_date;
EnableForceOuter is on
Access Path:
+-SORT [Cost: 7K, Rows: 100K (NO STATISTICS)] (PATH ID: 1)
| Order: sales.store_key ASC, sales.sale_date ASC
| Execute on: All Nodes
| +---> JOIN HASH [Cost: 5K, Rows: 100K (NO STATISTICS)] (PATH ID: 2) Outer (BROADCAST)(LOCAL ROUND ROBIN)
| | Join Cond: (sales.product_key = products.product_key)
| | Execute on: All Nodes
| | +-- Outer -> STORAGE ACCESS for sales [Cost: 2K, Rows: 100K (NO STATISTICS)] (PATH ID: 3)
| | | Projection: store.store_sales_b0
| | | Materialize: sales.sale_date, sales.store_key, sales.product_key, sales.sales_quantity
| | | Filter: (sales.sale_date = '2014-12-01'::date)
| | | Execute on: All Nodes
| | +-- Inner -> STORAGE ACCESS for products [Cost: 177, Rows: 60K (NO STATISTICS)] (PATH ID: 4)
| | | Projection: public.products_b0
| | | Force outer level: 1
| | | Materialize: products.product_key, products.product_description
| | | Execute on: All Nodes
如果将 public.products 的 force_outer 设置更改为 8，Vertica 会创建将 public.products 指定为外部输入的另一个查询计划：
=> ALTER TABLE public.products FORCE OUTER 8;
ALTER TABLE
=> EXPLAIN SELECT s.store_key, p.product_description, s.sales_quantity, s.sale_date
FROM store.store_sales s JOIN public.products p ON s.product_key=p.product_key
WHERE s.sale_date='2014-12-01' ORDER BY s.store_key, s.sale_date;
EnableForceOuter is on
Access Path:
+-SORT [Cost: 7K, Rows: 100K (NO STATISTICS)] (PATH ID: 1)
| Order: sales.store_key ASC, sales.sale_date ASC
| Execute on: All Nodes
| +---> JOIN HASH [Cost: 5K, Rows: 100K (NO STATISTICS)] (PATH ID: 2) Inner (BROADCAST)
| | Join Cond: (sales.product_key = products.product_key)
| | Materialize at Output: products.product_description
| | Execute on: All Nodes
| | +-- Outer -> STORAGE ACCESS for products [Cost: 20, Rows: 60K (NO STATISTICS)] (PATH ID: 3)
| | | Projection: public.products_b0
| | | Force outer level: 8
| | | Materialize: products.product_key
| | | Execute on: All Nodes
| | | Runtime Filter: (SIP1(HashJoin): products.product_key)
| | +-- Inner -> STORAGE ACCESS for sales [Cost: 2K, Rows: 100K (NO STATISTICS)] (PATH ID: 4)
| | | Projection: store.store_sales_b0
| | | Materialize: sales.sale_date, sales.store_key, sales.product_key, sales.sales_quantity
| | | Filter: (sales.sale_date = '2014-12-01'::date)
| | | Execute on: All Nodes
Vertica 在执行以下操作时会忽略 force_outer 设置：
外联接：无论已联接表的 force_outer 设置如何，Vertica 通常都会遵循 OUTER JOIN 子句。
MERGE 语句联接。
包含
SYNTACTIC_JOIN 提示的查询。
半联接查询，例如
SEMI JOIN。
联接到子查询，其中有一个子查询总是按 force_outer 设置为 5 进行处理，而不论在此子查询中已联接表的 force_outer 设置如何。此设置决定了相对于其他联接输入，将子查询指定为内部还是外部输入。如果联接两个子查询，优化器会决定哪个是内部输入，哪个是外部输入。
Vertica 为联接 ON 子句中的 <、<=、>、>= 和 BETWEEN 谓词进行了性能优化。如果一个表的列限定在另一个表的两个列所指定的范围内，这些优化将特别有用。
多个连续的键值可映射到相同的维度值。例如，以 IPv4 地址表及其所有者为例。因为大型 IP 地址子网（范围）可属于同一个所有者，所以此维度可表示为：
=> CREATE TABLE ip_owners(
ip_start INTEGER,
ip_end INTEGER,
owner_id INTEGER);
=> CREATE TABLE clicks(
ip_owners INTEGER,
dest_ip INTEGER);
将点击流与其目标相关联的查询可以使用与以下使用范围优化的联接类似的联接：
=> SELECT owner_id, COUNT(*) FROM clicks JOIN ip_owners
ON clicks.dest_ip BETWEEN ip_start AND ip_end
GROUP BY owner_id;
<、<=、>、>= 或 BETWEEN 运算符必须作为最高级别的连接谓词显示，才能使范围联接优化起作用，如以下示例所示：
`BETWEEN `作为唯一的谓词：
```
=> SELECT COUNT(*) FROM fact JOIN dim
ON fact.point BETWEEN dim.start AND dim.end;
```
比较运算符作为最高级别的谓词（在 `AND` 内）：
```
=> SELECT COUNT(*) FROM fact JOIN dim
ON fact.point > dim.start AND fact.point < dim.end;
```
`BETWEEN `作为最高级别的谓词（在 `AND` 内）：
```
=> SELECT COUNT(*) FROM fact JOIN dim
ON (fact.point BETWEEN dim.start AND dim.end) AND fact.c <> dim.c;
```
查询未优化，因为 `OR` 是最高级别的谓词（反意连接词）：
```
=> SELECT COUNT(*) FROM fact JOIN dim
ON (fact.point BETWEEN dim.start AND dim.end) OR dim.end IS NULL;
```
在许多用例中，范围联接查询中的表达式都得到了优化。
如果范围列可以包含 NULL 值（指示它们是开放式的），则将 NULL 替换为非常大或非常小的值，即可使用范围联接优化：
=> SELECT COUNT(*) FROM fact JOIN dim
ON fact.point BETWEEN NVL(dim.start, -1) AND NVL(dim.end, 1000000000000);
如果同一个 ON 子句中有一个以上范围谓词集，谓词的指定顺序可能会影响优化的效果：
=> SELECT COUNT(*) FROM fact JOIN dim ON fact.point1 BETWEEN dim.start1 AND dim.end1
AND fact.point2 BETWEEN dim.start2 AND dim.end2;
优化器会选择第一个范围进行优化，因此编写查询，确保让您最想优化的范围最先出现在语句中。
物理架构的任何特性都不会直接影响范围联接优化的使用；不需要对架构进行调试即可受益于优化。
范围联接优化可应用于不包含任何其他谓词的联接，也可应用于 HASH 或 MERGE 联接。
要确定优化是否正在使用，请在 EXPLAIN 计划中搜索 RANGE。
事件序列联接是一个 Vertica SQL 扩展，它可以在两个系列的度量间隔并不完全一致（例如时间戳不匹配）时分析这两个系列。您可以直接比较两个系列的值，而不是将系列都标准化为相同的度量间隔。
事件序列联接是 外联接 的扩展，但它不会在存在不匹配时用 NULL 值填充非保留侧，而是用根据以前值获得的插值填充非保留侧。
编写常规联接与事件序列联接的不同之处在于 ON 子句中使用的 INTERPOLATE 谓词。例如，以下两个语句就说明了这一不同之处，更多详情请参阅编写事件系列联接。
与常规联接类似，事件序列联接具有内部和外部联接模式，这在之后的主题中会进行介绍。
有关完整的语法（包括注释和限制）请参阅 INTERPOLATE
如果您不打算运行查询，只想查看示例，则可以跳过此主题，直接转至编写事件系列联接。
后面的示例使用以下 hTicks 表和 aTicks 表的架构：
尽管 TIMESTAMP 更常用于事件序列列，但为了让输出更简单，此主题中的示例使用了 TIME。
这两个表的输出：
完全外部联接显示了时间戳中的间隙：
=> SELECT * FROM hTicks h FULL OUTER JOIN aTicks a ON h.time = a.time;
stock | time | price | stock | time | price
-------+----------+-------+-------+----------+--------
HPQ | 12:00:00 | 50.00 | ACME | 12:00:00 | 340.00
HPQ | 12:01:00 | 51.00 | | |
HPQ | 12:05:00 | 51.00 | ACME | 12:05:00 | 333.80
HPQ | 12:05:00 | 51.00 | ACME | 12:05:00 | 340.20
HPQ | 12:06:00 | 52.00 | | |
| | | ACME | 12:03:00 | 340.10
(6 rows)
后面的示例使用以下 hTicks 表和 aTicks 表。
这两个表的输出：
完全外部联接显示了时间戳中的间隙：
=> SELECT * FROM bid b FULL OUTER JOIN ask a ON b.time = a.time;
stock | time | price | stock | time | price
-------+----------+--------+-------+----------+--------
HPQ | 12:00:00 | 100.10 | ACME | 12:00:00 | 80.00
HPQ | 12:01:00 | 100.00 | HPQ | 12:01:00 | 101.00
ACME | 12:00:00 | 80.00 | ACME | 12:00:00 | 80.00
ACME | 12:03:00 | 79.80 | | |
ACME | 12:05:00 | 79.90 | | |
| | | ACME | 12:02:00 | 75.00
(6 rows)
此主题中的示例包含时间戳不匹配项，就像在现实情形中可能会遇到的一样；例如，在没有交易时，股票在一段时间内可能会处于非活动状态，这时要想比较时间戳不匹配的两支股票会有一定挑战。
如示例 ticks 架构所述，表 hTicks 在 12:02、12:03 和 12:04 缺少输入行，表 aTicks 在 12:01、12:02 和 12:04 缺少输入行。
此查询使用传统的全外联接，在 hTicks 表和 aTicks 表之间找到了位于 12:00 和 12:05 的匹配项，然后用 NULL 值填充缺失的数据点。
=> SELECT * FROM hTicks h FULL OUTER JOIN aTicks a ON (h.time = a.time);
stock | time | price | stock | time | price
-------+----------+-------+-------+----------+--------
HPQ | 12:00:00 | 50.00 | ACME | 12:00:00 | 340.00
HPQ | 12:01:00 | 51.00 | | |
HPQ | 12:05:00 | 51.00 | ACME | 12:05:00 | 333.80
HPQ | 12:05:00 | 51.00 | ACME | 12:05:00 | 340.20
HPQ | 12:06:00 | 52.00 | | |
| | | ACME | 12:03:00 | 340.10
(6 rows)
要为缺失的数据点将空白替换为内插值，可使用 INTERPOLATE 谓词创建 事件序列联接。联接条件仅限于 ON 子句，该子句会在两个输入表的时间戳列中对等同谓词求值。换句话说，对于外表 hTicks 中的每个行，为内表 aTicks 中每个行的每个组合对 ON 子句谓词进行求值。
简单地重写全外联接可将 INTERPOLATE 谓词与所需的 PREVIOUS VALUE 关键字结合使用。请注意，对于事件序列数据，在事件序列数据上执行全外联接是最常见的场景，在此场景中，您可以同时保留两个表中的所有行。
=> SELECT * FROM hTicks h FULL OUTER JOIN aTicks a
ON (h.time INTERPOLATE PREVIOUS VALUE a.time);
Vertica 使用此表以前的值内插缺失数据（在全外联接中显示为 NULL）：
如果查看普通全外联接的输出，您可以发现这两个表在 12:00 和 12:05 时间列中存在一个匹配，但在 12:01，则不存在 ACME 的条目记录。因此，相关操作会根据 aTicks 表中以前的值为 ACME (ACME,12:00,340) 内插一个值。
您也可以使用左外联接和右外联接。例如，您可能决定仅保留 hTicks 的值。因此，您将写入左外联接：
=> SELECT * FROM hTicks h LEFT OUTER JOIN aTicks a
ON (h.time INTERPOLATE PREVIOUS VALUE a.time);
stock | time | price | stock | time | price
-------+----------+-------+-------+----------+--------
HPQ | 12:00:00 | 50.00 | ACME | 12:00:00 | 340.00
HPQ | 12:01:00 | 51.00 | ACME | 12:00:00 | 340.00
HPQ | 12:05:00 | 51.00 | ACME | 12:05:00 | 333.80
HPQ | 12:05:00 | 51.00 | ACME | 12:05:00 | 340.20
HPQ | 12:06:00 | 52.00 | ACME | 12:05:00 | 340.20
(5 rows)
以下数据与使用传统的左外联接得到的数据相似：
=> SELECT * FROM hTicks h LEFT OUTER JOIN aTicks a ON h.time = a.time;
stock | time | price | stock | time | price
-------+----------+-------+-------+----------+--------
HPQ | 12:00:00 | 50.00 | ACME | 12:00:00 | 340.00
HPQ | 12:01:00 | 51.00 | | |
HPQ | 12:05:00 | 51.00 | ACME | 12:05:00 | 333.80
HPQ | 12:05:00 | 51.00 | ACME | 12:05:00 | 340.20
HPQ | 12:06:00 | 52.00 | | |
(5 rows)
请注意，右外联接与保存表的行为相同，只是顺序相反。
请注意，如果忽视所有空白，INNER 事件序列联接的行为方式与普通的 ANSI SQL-99 联接相同。因此，这时不会内插任何内容，而且以下两个查询等效，都会返回相同的结果集。
普通的内联接：
=> SELECT * FROM HTicks h JOIN aTicks a
ON (h.time INTERPOLATE PREVIOUS VALUE a.time);
stock | time | price | stock | time | price
-------+----------+-------+-------+----------+--------
HPQ | 12:00:00 | 50.00 | ACME | 12:00:00 | 340.00
HPQ | 12:05:00 | 51.00 | ACME | 12:05:00 | 333.80
HPQ | 12:05:00 | 51.00 | ACME | 12:05:00 | 340.20
(3 rows)
事件序列内联接：
=> SELECT * FROM hTicks h INNER JOIN aTicks a ON (h.time = a.time);
stock | time | price | stock | time | price
-------+----------+-------+-------+----------+--------
HPQ | 12:00:00 | 50.00 | ACME | 12:00:00 | 340.00
HPQ | 12:05:00 | 51.00 | ACME | 12:05:00 | 333.80
HPQ | 12:05:00 | 51.00 | ACME | 12:05:00 | 340.20
(3 rows)
使用 bid 表和 ask 表的示例架构，编写全外联接以内插缺失的数据点：
=> SELECT * FROM bid b FULL OUTER JOIN ask a
ON (b.stock = a.stock AND b.time INTERPOLATE PREVIOUS VALUE a.time);
在下列输出中，股票 HPQ 的第一行显示了 NULL，因为在 12:01 之前不存在 HPQ 的条目记录。
stock | time | price | stock | time | price
-------+----------+--------+-------+----------+--------
ACME | 12:00:00 | 80.00 | ACME | 12:00:00 | 80.00
ACME | 12:00:00 | 80.00 | ACME | 12:02:00 | 75.00
ACME | 12:03:00 | 79.80 | ACME | 12:02:00 | 75.00
ACME | 12:05:00 | 79.90 | ACME | 12:02:00 | 75.00
HPQ | 12:00:00 | 100.10 | | |
HPQ | 12:01:00 | 100.00 | HPQ | 12:01:00 | 101.00
(6 rows)
另外，请注意 ask 表的同一个行 (ACME,12:02,75) 出现了三次。第一次出现是因为 bid 表中没有 ask 中的行，因此 Vertica 使用 12:02 的 ACME 值 (75.00) 插入了缺失值。第二次出现是因为 bid 中的行 (ACME,12:05,79.9) 在 ask 中没有相应的匹配项。ask 中包含 (ACME,12:02,75) 的行是最接近的行，因此使用它来插入值。
如果编写普通的全外联接，则可以发现哪些地方出现了不匹配的时间戳：
=> SELECT * FROM bid b FULL OUTER JOIN ask a ON (b.time = a.time);
stock | time | price | stock | time | price
-------+----------+--------+-------+----------+--------
ACME | 12:00:00 | 80.00 | ACME | 12:00:00 | 80.00
ACME | 12:03:00 | 79.80 | | |
ACME | 12:05:00 | 79.90 | | |
HPQ | 12:00:00 | 100.10 | ACME | 12:00:00 | 80.00
HPQ | 12:01:00 | 100.00 | HPQ | 12:01:00 | 101.00
| | | ACME | 12:02:00 | 75.00
(6 rows)