EXCEPT 子句
合并两个或多个 SELECT 查询。EXCEPT 返回在右侧查询中未查找到的左侧查询特定结果。
注意
MINUS 是 EXCEPT 的别名。语法
SELECT
EXCEPT except‑query[...]
[ ORDER BY { column-name | ordinal-number } [ ASC | DESC ] [,...] ]
[ LIMIT { integer | ALL } ]
[ OFFSET integer ]
注意
-
使用 EXCEPT 子句可从 SELECT 语句中筛选出特定结果。EXCEPT 查询对两个或更多 SELECT 查询的结果执行操作。它只返回不同时存在于右侧查询中的左侧查询中的行。
-
除非用括号另作指示，否则 Vertica 会从左向右评估同一 SELECT 查询中的多个 EXCEPT 子句。
-
不能将 ALL 关键字与 EXCEPT 查询一起使用。
-
各 SELECT 语句的结果必须是可合并的。各语句必须返回相同数量的列，且对应列的数据类型必须兼容。例如，不能对 INTEGER 类型的列和 VARCHAR 类型的列使用 EXCEPT 子句。如果语句不满足这些条件，Vertica 将返回错误。
注意
数据类型强制转换表列出了哪些数据类型可以转换为其他数据类型。如果一种数据类型可转换为其他数据类型，则这两种数据类型兼容。 -
您可以在 FROM、WHERE 和 HAVING 子句中使用 EXCEPT。
-
您可以通过在语句中加入 ORDER BY 操作的方式对 EXCEPT 操作的结果进行排序。编写 ORDER BY 列表时，请指定最左侧 SELECT 语句中的列名称，或者指定指示排序依据列的位置的整数。
-
由于最右侧的查询指定 Vertica 对 EXCEPT 操作的结果执行操作，因此 EXCEPT 查询中最右侧的 ORDER BY、LIMIT 或 OFFSET 子句不需要加括号。包含在 SELECT 查询中且之前出现在 EXCEPT 查询中的任何 ORDER BY、LIMIT 或 OFFSET 子句都必须括在圆括号中。
-
Vertica 支持 EXCEPT 非相关子查询谓词。例如：
=> SELECT * FROM T1 WHERE T1.x IN (SELECT MAX(c1) FROM T2 EXCEPT SELECT MAX(cc1) FROM T3 EXCEPT SELECT MAX(d1) FROM T4);
示例
假设有下面三个表：
Company_A
Id | emp_lname | dept | sales
------+-----------+----------------+-------
1234 | Stephen | auto parts | 1000
5678 | Alice | auto parts | 2500
9012 | Katherine | floral | 500
3214 | Smithson | sporting goods | 1500
(4 rows)
Company_B
Id | emp_lname | dept | sales
------+-----------+-------------+-------
4321 | Marvin | home goods | 250
8765 | Bob | electronics | 20000
9012 | Katherine | home goods | 500
3214 | Smithson | home goods | 1500
(4 rows)
Company_C
Id | emp_lname | dept | sales
------+-----------+----------------+-------
3214 | Smithson | sporting goods | 1500
5432 | Madison | sporting goods | 400
7865 | Cleveland | outdoor | 1500
1234 | Stephen | floral | 1000
(4 rows)
以下查询将返回存在于 Company_A 内但不存在于 Company_B 内的员工的 ID 和姓氏：
=> SELECT id, emp_lname FROM Company_A
EXCEPT
SELECT id, emp_lname FROM Company_B;
id | emp_lname
------+-----------
1234 | Stephen
5678 | Alice
(2 rows)
以下查询将按照员工的姓氏对上一查询的结果进行排序：
=> SELECT id, emp_lname FROM Company_A
EXCEPT
SELECT id, emp_lname FROM Company_B
ORDER BY emp_lname ASC;
id | emp_lname
------+-----------
5678 | Alice
1234 | Stephen
(2 rows)
如果按列位置排序，查询将返回相同结果：
=> SELECT id, emp_lname FROM Company_A
EXCEPT
SELECT id, emp_lname FROM Company_B
ORDER BY 2 ASC;
id | emp_lname
------+-----------
5678 | Alice
1234 | Stephen
(2 rows)
以下查询将返回存在于 Company_A 内但不存在于 Company_B 或 Company_C 内的员工的 ID 和姓氏：
=> SELECT id, emp_lname FROM Company_A
EXCEPT
SELECT id, emp_lname FROM Company_B
EXCEPT
SELECT id, emp_lname FROM Company_C;
id | emp_lname
------+-----------
5678 | Alice
(1 row)
以下查询将显示不匹配数据类型的结果：
=> SELECT id, emp_lname FROM Company_A
EXCEPT
SELECT emp_lname, id FROM Company_B;
ERROR 3429: For 'EXCEPT', types int and varchar are inconsistent
DETAIL: Columns: id and emp_lname
以下示例使用 VMart 示例数据库，将返回通过商店购买商品且购买金额已超过 500 美元的康涅狄格州客户，但不包括现金支付的客户：
=> SELECT 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
EXCEPT
SELECT customer_key FROM store.store_sales_fact
WHERE tender_type = 'Cash')
AND customer_state = 'CT';
customer_key | customer_name
--------------+----------------------
15084 | Doug V. Lampert
21730 | Juanita F. Peterson
24412 | Mary U. Garnett
25840 | Ben Z. Taylor
29940 | Brian B. Dobisz
32225 | Ruth T. McNulty
33127 | Darlene Y. Rodriguez
40000 | Steve L. Lewis
44383 | Amy G. Jones
46495 | Kevin H. Taylor
(10 rows)