SQL EXCEPT子句

通常，我们使用JOIN子句从多个表中获取组合结果。有时，我们需要一个结果集，其中包含来自一个表而在另一个表中不可用的记录。在这种情况下，SQL提供了EXCEPT子句/运算符。

SQL中的EXCEPT子句广泛用于过滤从多个表中的记录。该语句首先组合两个SELECT语句并返回不在第二个SELECT查询结果中出现的第一个SELECT查询的记录。换句话说，它从第一个SELECT查询中检索所有行，同时删除第二个查询结果中的冗余行。

此语句的行为与数学中的减号运算符相同。本文将通过基本示例说明如何使用SQL EXCEPT子句。

SQL EXCEPT规则

在使用SQL中的EXCEPT语句之前，我们应该考虑以下规则：

• 所有SELECT语句中的列数和顺序必须相同。
• 相应列的数据类型应该相同或兼容。
• 两个SELECT语句的各自列中的字段不能相同。

SQL EXCEPT语法

以下语法说明了EXCEPT子句的使用方法：

SELECT column_lists from table_name1
EXCEPT
SELECT column_lists from table_name2;

注意：值得注意的是MySQL不支持EXCEPT子句。所以这里我们将使用PostgreSQL数据库来解释SQL EXCEPT示例。

下面的图片解释了在两个表T1和T2中EXCEPT操作的工作原理：

说明：

• 表T1包含数据1、2和3。
• 表T2包含数据2、3和4。

当我们在这些表上执行EXCEPT查询时，我们将获得1，它是T1中唯一的数据，并且在T2中找不到。

SQL EXCEPT示例

让我们首先使用以下脚本创建两个表：

表：Customer

CREATE TABLE public.customer
(
    id integer NOT NULL,
    name text COLLATE pg_catalog."default" NOT NULL,
    age integer NOT NULL,
    salary real NOT NULL
)

表：orders

CREATE TABLE public."orders"
(
    order_id integer NOT NULL,
    date date NOT NULL,
    cust_id integer NOT NULL,
    amount real NOT NULL,
    CONSTRAINT "order_pkey" PRIMARY KEY ("order_id")
)

接下来，我们将按照以下方式向两个表中插入一些记录：

表：customer

INSERT INTO public.customer(
    id, name, age, salary)
    VALUES (101, 'John', 24, 20000)
    (102, 'Mike', 22, 25000),
    (103, 'Emily', 24, 22000),
    (104, 'James', 20, 30000),
    (105, 'Sophia', 21, 35000);

表：orders

INSERT INTO public.orders(
    order_id, date, cust_id, amount)
    VALUES (1, '2009-10-08', 103, 1500),
    (2, '2009-11-06', 103, 1000),
    (3, '2009-12-05', 102, 2500),
    (4, '2009-09-08', 101, 1800);

接下来，我们将使用SELECT语句来验证记录。请参见下面的图像：

让我们来看看一个使用这些表的SQL EXCEPT的例子。假设我们想要在我们的SELECT语句中将这些表连接起来，如下所示：

SELECT  id, name, amount, date
   FROM customer
   LEFT JOIN orders
   ON customer.id = orders. order_id
EXCEPT
   SELECT  id, name, amount, date
   FROM customer
   RIGHT JOIN orders
   ON customer.id = orders. order_id;

将会产生以下输出：

运用ORDER BY子句的EXCEPT操作符

如果我们想要对通过EXCEPT操作符得到的结果集进行排序，我们需要在查询中添加 ORDER BY子句 。例如，以下示例连接了两个表并按照它们的名称进行升序排序结果集：

SELECT  id, name, amount, date
   FROM customer
   LEFT JOIN orders
   ON customer.id = orders. order_id
EXCEPT
   SELECT  id, name, amount, date
   FROM customer
   RIGHT JOIN orders
   ON customer.id = orders. order_id
   ORDER BY name;

它将产生以下输出：

在单个表中使用EXCEPT语句

通常情况下，我们会在两个表中使用EXCEPT语句，但是我们也可以用它们来从单个表中筛选记录。例如，下面的EXCEPT语句将返回customer表中年龄大于21的所有记录：

SELECT id, name, age, salary FROM customer
EXCEPT
SELECT id, name, age, salary FROM customer WHERE age > 21;

在这个脚本中，第一个SELECT查询返回customer表中的所有记录，第二个查询返回年龄大于21的所有记录。接下来，EXCEPT语句使用这两个SELECT语句过滤记录，并只返回年龄大于21的行。

EXCEPT与NOT IN语句有什么区别？

EXCEPT与NOT IN语句的区别如下：

• EXCEPT子句会自动删除结果集中的所有重复记录，而NOT IN不会删除重复记录。
• EXCEPT子句可以在单个或多个列中进行比较，而NOT IN子句只能在单个列中进行比较。