PostgreSQL - EXISTS 运算符

PostgreSQL EXISTS 运算符

PostgreSQL 中的 EXISTS 运算符是一个布尔运算符，用于测试子查询中是否存在行。如果子查询返回一行或多行，它将返回 TRUE；否则返回 FALSE。EXISTS 的效率很高，因为它一旦找到第一条匹配的行就会停止处理子查询。

语法

SELECT column_list
FROM table_name
WHERE EXISTS (subquery);

EXISTS 的工作原理

• 子查询会针对外部查询的每一行执行一次。
• 如果子查询至少返回一行，EXISTS 将评估为 TRUE，并将外部查询的当前行包含在结果集中。
• 如果子查询未返回任何行，EXISTS 将评估为 FALSE，并且当前行将被丢弃。
• 它通常与相关子查询一起使用，子查询依赖于外部查询中的值。

示例：查找已下订单的客户

我们来看两个表：customers（客户）和 orders（订单）。我们想找出所有至少下过一个订单的客户。

步骤 1：创建并填充表

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

INSERT INTO customers (customer_name) VALUES
('Acme Corp'),
('Globex Inc'),
('Stark Industries');

INSERT INTO orders (customer_id, order_date) VALUES
(1, '2023-10-26'),
(3, '2023-10-25');

步骤 2：使用 EXISTS 查找有订单的客户

此查询选择存在订单的客户。

SELECT c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id -- 这种关联是关键
);

最佳实践：在 EXISTS 子查询内部使用 SELECT 1 是一种约定。由于 EXISTS 只关心行的存在性，而不关心其内容，因此无需选择实际的数据列。SELECT 1 是表达此目的最有效的方式。

查询结果如下：

  customer_name
------------------
 Acme Corp
 Stark Industries
(2 rows)

步骤 3：使用 NOT EXISTS

NOT EXISTS 运算符是 EXISTS 的逆操作。它用于查找外部查询中在子查询中没有匹配项的行。让我们找出从未下过订单的客户。

SELECT c.customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

此查询结果如下：

 customer_name
---------------
 Globex Inc
(1 row)

EXISTS、IN 与 JOIN 的对比

这三种构造有时可以实现类似的结果，但它们的性能和意图有所不同。

• EXISTS：最适合在子查询结果集可能很大时检查是否存在。一旦找到匹配项，它就会停止。它不返回子查询中的数据。
• IN：最适合子查询返回少量固定值的情况。整个子查询通常会先执行，其结果存储在内存中进行比较。对于大型子查询结果，其效率可能低于 EXISTS。
• JOIN（特别是 INNER JOIN）：当你需要从两个表中选择列时使用。如果你只需要进行过滤，EXISTS 通常性能更高，意图也更清晰，因为如果“多”侧有多个匹配项，JOIN 可能会从“一”侧产生重复的行，需要额外的 DISTINCT。

对于查找有订单的客户，EXISTS 通常是最地道且性能最佳的选择。