PHP小码农

Appearance

BETWEEN / IN / ANY / ALL ​

BETWEEN、IN、ANY、ALL 是四组用于「批量比较」的谓词与量词。它们把多个比较条件压缩成一行可读的 SQL，但各自的边界语义、NULL 行为、索引可用性差异很大。最典型的坑是：BETWEEN 是闭区间，处理日期时间时容易多算一天；NOT IN 遇 NULL 会返回空集；ALL 在空集上恒为 TRUE。本节把这些语义一次讲透。

前置知识

阅读本节前，建议先了解：IS NULL 与 NULL 判断

基础概念 ​

概念术语说明
范围谓词BETWEEN判断值是否落在 [下界, 上界] 的闭区间内
对称范围BETWEEN SYMMETRIC自动交换上下界，不要求下界小于上界
成员谓词IN判断值是否等于列表或子查询结果中的任意一个
任意量词ANY / SOME与子查询或数组逐项比较，只要有一项满足即为真
全称量词ALL与子查询或数组逐项比较，必须全部满足才为真
量词比较quantified comparison形如 x > ANY(...)、x <> ALL(...) 的组合比较
闭区间closed interval包含两个端点，即 >= lower AND <= upper
半开区间half-open interval只含左端点的 [lower, upper)，日期范围常用
空集语义empty set semantics= ANY(空集) 为 FALSE，= ALL(空集) 恒为 TRUE
数组展开array expansionANY(数组) / ALL(数组) 直接对数组元素做比较

BETWEEN、IN 本质上都是语法糖：

  • a BETWEEN x AND y ⟺ a >= x AND a <= y
  • a IN (x, y, z) ⟺ a = x OR a = y OR a = z
  • a > ANY (SELECT ...) ⟺ a > min(...) 的语义（存在性）
  • a > ALL (SELECT ...) ⟺ a > max(...) 的语义（全称性）

理解这些等价关系，就能准确预判它们的 NULL 行为与索引表现。

sql
-- 四组写法的最小示例
SELECT
  50 BETWEEN 10 AND 100        AS between_demo,   -- true
  3 IN (1, 2, 3)               AS in_demo,        -- true
  50 > ANY (ARRAY[10, 20, 30]) AS any_demo,       -- true
  50 > ALL (ARRAY[10, 20, 30]) AS all_demo;       -- true

语法与用法 ​

形式等价写法说明
expr BETWEEN a AND bexpr >= a AND expr <= b闭区间
expr NOT BETWEEN a AND bexpr < a OR expr > b区间外
expr BETWEEN SYMMETRIC a AND b自动排序上下界上下界顺序无关
expr IN (v1, v2, ...)expr = v1 OR expr = v2 ...常量列表
expr IN (SELECT ...)等价于半连接子查询
expr = ANY (array)数组元素逐个比较数组专用
expr > ALL (SELECT ...)全称量词子查询或数组
expr = SOME (array)ANY 的同义词兼容 SQL 标准
sql
-- 最小可运行示例：无需建表即可执行
SELECT
  5 BETWEEN 1 AND 10                    AS a,  -- true
  5 NOT BETWEEN 1 AND 10                AS b,  -- false
  5 BETWEEN SYMMETRIC 10 AND 1          AS c,  -- true（自动交换）
  'b' IN ('a', 'b', 'c')                AS d,  -- true
  5 = ANY (ARRAY[1, 5, 9])              AS e,  -- true
  5 < ALL (ARRAY[6, 7, 8])              AS f;  -- true

详细说明 ​

BETWEEN 的闭区间语义 ​

BETWEEN 同时包含两个端点。处理整数时通常没问题，但处理日期时间时要格外小心：date 与 timestamp 的「上界那一天」含义完全不同。

sql
-- 对整数：包含 10 和 100
SELECT 10 BETWEEN 10 AND 100 AS lo_included,   -- true
       100 BETWEEN 10 AND 100 AS hi_included;  -- true

-- 对 timestamp：上界是「那一刻」，不是「那一天」
-- 以下查询会漏掉 2025-03-31 当天 00:00:00 之后的记录
SELECT * FROM orders
WHERE created_at BETWEEN '2025-03-01' AND '2025-03-31';  -- 不推荐

正确做法有两种：要么把上界写成次日零点并用 <，要么把类型收窄为 date。

sql
-- 推荐：半开区间 [起, 止)
SELECT * FROM orders
WHERE created_at >= '2025-03-01'
  AND created_at <  '2025-04-01';

-- 或：先转成 date 再用 BETWEEN
SELECT * FROM orders
WHERE created_at::date BETWEEN '2025-03-01' AND '2025-03-31';

BETWEEN SYMMETRIC ​

普通 BETWEEN 要求下界小于等于上界，否则结果恒为 FALSE。BETWEEN SYMMETRIC 会自动比较两个边界并把小的当上界、大的当下界，适合边界顺序不固定的场景。

sql
SELECT 5 BETWEEN 10 AND 1           AS normal,     -- false（下界大于上界）
       5 BETWEEN SYMMETRIC 10 AND 1 AS symmetric;  -- true

IN：常量列表与子查询 ​

IN 支持两种右侧写法：常量列表与子查询。两者都可被优化器改写成 ANY 或半连接。

sql
-- 常量列表
SELECT * FROM products WHERE category IN ('显示器', '键盘', '鼠标');

-- 子查询
SELECT * FROM products
WHERE id IN (SELECT product_id FROM orders WHERE qty > 1);

IN 内部使用「等值比较」，因此 NULL 行为与 = 一致：x IN (1, NULL) 在 x 不等于任何非 NULL 值时结果为 NULL，会被 WHERE 过滤掉。

NOT IN 与 NULL 陷阱 ​

NOT IN 是 IN 取反，但它遇 NULL 时的行为极具破坏性。

sql
-- 若子查询返回任何 NULL，整个 NOT IN 结果为「无行」
SELECT * FROM products
WHERE id NOT IN (SELECT product_id FROM orders);  -- 错误！可能返回空集

-- 原因：展开后为 id <> 1 AND id <> 2 AND ... AND id <> NULL
-- 最后一项恒为 NULL，AND 链结果只能是 NULL 或 FALSE，永远不为 TRUE

修复方式有三种，推荐第一种：

sql
-- 方案 A（推荐）：改用 NOT EXISTS
SELECT p.* FROM products p
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.product_id = p.id);

-- 方案 B：在子查询中排除 NULL
SELECT * FROM products
WHERE id NOT IN (SELECT product_id FROM orders WHERE product_id IS NOT NULL);

-- 方案 C：用 ALL 显式表达（语义较绕，不推荐）
SELECT * FROM products p
WHERE p.id <> ALL (SELECT o.product_id FROM orders o WHERE o.product_id IS NOT NULL);

ANY / SOME 与 ALL 量词 ​

ANY（SOME 是其同义词，二者完全等价）与 ALL 必须配合比较运算符使用，右侧可以是子查询或数组。

语义规则：

  • x op ANY (集合)：集合中存在一个元素使 x op 元素 为真，结果即真。
  • x op ALL (集合)：集合中所有元素都使 x op 元素 为真，结果才为真。
sql
-- 找价格高于「任一」订单金额的商品（即高于最小金额）
SELECT * FROM products
WHERE price > ANY (SELECT amount FROM orders);

-- 找价格高于「所有」订单金额的商品（即高于最大金额，存在性要求高）
SELECT * FROM products
WHERE price > ALL (SELECT amount FROM orders);

= ANY 与 IN 完全等价，<> ALL 与 NOT IN 完全等价：

sql
-- 以下两组查询语义完全相同
SELECT * FROM products WHERE id = ANY (SELECT product_id FROM orders);
SELECT * FROM products WHERE id IN (SELECT product_id FROM orders);

SELECT * FROM products WHERE id <> ALL (SELECT product_id FROM orders);
SELECT * FROM products WHERE id NOT IN (SELECT product_id FROM orders);

ANY / ALL 配合数组 ​

当右侧是数组时，ANY 与 ALL 无需子查询，直接比较数组元素，非常简洁。

sql
-- 判断值是否在数组中
SELECT * FROM products WHERE id = ANY (ARRAY[1, 3, 5]);

-- 判断值是否大于数组中所有元素（即大于最大值）
SELECT * FROM products WHERE price > ALL (ARRAY[100, 200, 300]);

-- 判断数组是否与目标有交集
SELECT * FROM products WHERE sku = ANY ('{SKU-1001,SKU-1002}'::text[]);

空集语义：= ALL(空集) 恒为 TRUE ​

这是 ALL 最容易踩的坑。当右侧集合为空时：

  • x = ANY(空集) 为 FALSE（没有任何元素满足）。
  • x = ALL(空集) 为 TRUE（「所有元素都满足」在空集上是空真，vacuous truth）。
sql
-- 空集上的 ANY 与 ALL
SELECT 5 = ANY (SELECT 1 WHERE false) AS any_on_empty,  -- false
       5 = ALL (SELECT 1 WHERE false) AS all_on_empty;  -- true

这会导致一个反直觉的结果：x > ALL(子查询) 在子查询无行时返回 TRUE，从而把所有行都选出来。

sql
-- 危险！orders 为空时，这条查询会返回 products 的全部行
SELECT * FROM products
WHERE price > ALL (SELECT amount FROM orders WHERE qty > 100);  -- 需谨慎

索引可用性与优化器改写 ​

BETWEEN 与 IN 通常能走索引：

  • BETWEEN 是范围条件，可走 B-tree 的范围扫描。
  • IN (常量列表) 会被改写成多个等值条件的 OR，也可能走 Bitmap 扫描。
  • IN (子查询) 通常被改写为半连接（Hash Semi Join 或 Nested Loop Semi Join）。
  • NOT IN / <> ALL 通常是反连接，遇 NULL 无法用「反半连接」优化，需全表扫描。
sql
-- BETWEEN 可走索引范围扫描
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM products WHERE price BETWEEN 100 AND 500;

-- NOT IN 含 NULL 时无法改写为高效反连接
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM products WHERE id NOT IN (SELECT product_id FROM orders);

用 NOT EXISTS 版本可以让优化器生成「反半连接」，性能通常更好：

sql
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM products p
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.product_id = p.id);

量词与 NULL 的组合规则 ​

ANY / ALL 遇 NULL 时遵循三值逻辑：

情形x = ANY(含 NULL)x = ALL(含 NULL)
存在匹配的非 NULL 元素TRUE取决于是否全部匹配
无匹配但含 NULLNULLNULL 或 FALSE
全为非 NULL 且全部不匹配FALSEFALSE
sql
-- ANY 遇 NULL：非 NULL 匹配即返回 true
SELECT 1 = ANY (ARRAY[1, NULL]) AS a;  -- true

-- ALL 遇 NULL：有非 NULL 不匹配即 false；否则可能为 NULL
SELECT 1 = ALL (ARRAY[1, NULL]) AS b,  -- NULL
       1 = ALL (ARRAY[2, NULL]) AS c;  -- false

实战示例 ​

步骤 1：建表并造数据 ​

sql
-- 统一的示例库
CREATE DATABASE demo;

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;

CREATE TABLE products (
  id       integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku      text    NOT NULL UNIQUE,
  name     text    NOT NULL,
  category text,
  price    numeric(10, 2),
  stock    integer NOT NULL DEFAULT 0,
  added_on date
);

CREATE TABLE orders (
  id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no   text NOT NULL,
  product_id integer,
  qty        integer NOT NULL,
  amount     numeric(10, 2),
  created_at timestamp NOT NULL DEFAULT now()
);

INSERT INTO products (sku, name, category, price, stock, added_on) VALUES
  ('SKU-1001', '27 英寸显示器', '显示器', 1299.00,  12, '2025-01-10'),
  ('SKU-1002', '机械键盘',       '键盘',    399.00,  30, '2025-02-01'),
  ('SKU-1003', '无线鼠标',       '鼠标',    159.00,   0, '2025-02-15'),
  ('SKU-1004', 'HDMI 线缆',      '线缆',     49.00, 200, '2025-03-01'),
  ('SKU-1005', 'USB 集线器',     '配件',     89.00,  50, '2025-03-20'),
  ('SKU-1006', '显卡支架',       '配件',    129.00,   8, '2025-04-05');

INSERT INTO orders (order_no, product_id, qty, amount, created_at) VALUES
  ('SO-2001', 1,    2, 2598.00, '2025-03-05 10:12:00'),
  ('SO-2002', 2,    1,  399.00, '2025-03-18 15:30:00'),
  ('SO-2003', NULL, 1,  159.00, '2025-03-31 23:59:00'),  -- product_id 为 NULL
  ('SO-2004', 3,    3,  477.00, '2025-04-01 08:00:00'),
  ('SO-2005', NULL, 2,  178.00, '2025-04-20 12:00:00');

步骤 2：BETWEEN 范围查询与日期陷阱 ​

sql
-- 价格区间查询（闭区间，含 100 与 500）
SELECT sku, name, price
FROM products
WHERE price BETWEEN 100 AND 500
ORDER BY price;
text
   sku    |    name    |  price
----------+------------+---------
 SKU-1006 | 显卡支架     |  129.00
 SKU-1003 | 无线鼠标     |  159.00
 SKU-1002 | 机械键盘     |  399.00
(3 rows)
sql
-- 日期陷阱演示：BETWEEN 上界是「那一刻」，会漏掉 3 月 31 日全天数据
SELECT count(*) AS between_rows
FROM orders
WHERE created_at BETWEEN '2025-03-01' AND '2025-03-31';
text
 between_rows
--------------
            2
(1 row)
sql
-- 推荐写法：半开区间，包含 3 月 31 日全天
SELECT count(*) AS half_open_rows
FROM orders
WHERE created_at >= '2025-03-01'
  AND created_at <  '2025-04-01';
text
 half_open_rows
----------------
              3
(1 row)

步骤 3：IN 与 NOT IN 陷阱修复 ​

sql
-- IN 常量列表
SELECT sku, name, category
FROM products
WHERE category IN ('键盘', '鼠标')
ORDER BY sku;
text
   sku    |   name   | category
----------+----------+----------
 SKU-1002 | 机械键盘  | 键盘
 SKU-1003 | 无线鼠标  | 鼠标
(2 rows)
sql
-- 陷阱：orders.product_id 含 NULL，NOT IN 返回空集
SELECT sku, name
FROM products
WHERE id NOT IN (SELECT product_id FROM orders);
text
 sku | name
-----+------
(0 rows)
sql
-- 修复：NOT EXISTS
SELECT p.sku, p.name
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.product_id = p.id)
ORDER BY p.sku;
text
   sku    |    name
----------+------------
 SKU-1004 | HDMI 线缆
 SKU-1005 | USB 集线器
 SKU-1006 | 显卡支架
(3 rows)

步骤 4：ANY / ALL 量词查询 ​

sql
-- 价格高于任意一笔订单金额（即 > min）
SELECT sku, name, price
FROM products
WHERE price > ANY (SELECT amount FROM orders)
ORDER BY price;
text
   sku    |    name    |  price
----------+------------+---------
 SKU-1003 | 无线鼠标     |  159.00
 SKU-1002 | 机械键盘     |  399.00
 SKU-1001 | 27 英寸显示器 | 1299.00
(3 rows)
sql
-- 价格高于所有订单金额（即 > max）
SELECT sku, name, price
FROM products
WHERE price > ALL (SELECT amount FROM orders)
ORDER BY price;
text
   sku    |    name    |  price
----------+------------+---------
 SKU-1001 | 27 英寸显示器 | 1299.00
(1 row)
sql
-- 删除 NULL 后，NOT IN 与 <> ALL 可正常工作
SELECT sku, name
FROM products
WHERE id <> ALL (SELECT product_id FROM orders WHERE product_id IS NOT NULL)
ORDER BY sku;
text
   sku    |    name
----------+------------
 SKU-1004 | HDMI 线缆
 SKU-1005 | USB 集线器
 SKU-1006 | 显卡支架
(3 rows)

步骤 5：空集语义与安全写法 ​

sql
-- 危险演示：子查询为空时 > ALL 返回所有行
SELECT count(*) AS all_rows_when_empty
FROM products
WHERE price > ALL (SELECT amount FROM orders WHERE qty > 100);
text
 all_rows_when_empty
---------------------
                   6
(1 row)
sql
-- 安全写法：显式判断子查询非空
SELECT count(*) AS safe_rows
FROM products
WHERE EXISTS (SELECT 1 FROM orders WHERE qty > 100)
  AND price > ALL (SELECT amount FROM orders WHERE qty > 100);
text
 safe_rows
-----------
         0
(1 row)

步骤 6：ANY 配合数组做批量筛选 ​

sql
-- 用数组代替一长串 OR
SELECT sku, name, price
FROM products
WHERE sku = ANY (ARRAY['SKU-1001', 'SKU-1003', 'SKU-1006'])
ORDER BY sku;
text
   sku    |    name     |  price
----------+-------------+---------
 SKU-1001 | 27 英寸显示器 | 1299.00
 SKU-1003 | 无线鼠标      |  159.00
 SKU-1006 | 显卡支架      |  129.00
(3 rows)
sql
-- 数组作为参数传入（应用层常用）
-- psql 中可直接用字面量
SELECT sku, name FROM products WHERE id = ANY ('{1,2,4}'::int[]) ORDER BY id;
text
   sku    |    name
----------+------------
 SKU-1001 | 27 英寸显示器
 SKU-1002 | 机械键盘
 SKU-1004 | HDMI 线缆
(3 rows)

注意事项 ​

  • BETWEEN 是闭区间，且上界是「那一刻」而非「那一天」。 处理 timestamp 时优先用半开区间 >= 起 AND < 止。
sql
-- 不推荐：会漏掉上界当天之后的数据
WHERE created_at BETWEEN '2025-03-01' AND '2025-03-31'
-- 推荐
WHERE created_at >= '2025-03-01' AND created_at < '2025-04-01'

  • NOT IN 遇 NULL 返回空集。 只要有 NULL 参与，结果就不可预期，一律改用 NOT EXISTS。

  • = ALL (空集) 恒为 TRUE。 使用 ALL 前要确认子查询不会返回空集，否则会意外选中所有行。

  • BETWEEN 的下界若大于上界，普通写法恒为 FALSE。 需要容忍乱序边界时才用 BETWEEN SYMMETRIC。

sql
SELECT 5 BETWEEN 10 AND 1;             -- false，不是「报错」
SELECT 5 BETWEEN SYMMETRIC 10 AND 1;   -- true

  • IN 的列表长度不宜过大。 几千个常量值会让解析与计划生成变慢，此时应改用临时表或 = ANY(数组)。

  • ANY 与 SOME 完全等价，不是「部分匹配」的意思，不要与 LIKE 混淆。

  • NOT IN 无法使用反半连接优化，性能通常劣于 NOT EXISTS，在大表上差距明显。

  • IN 子查询的列必须非 NULL 才安全，即使是 IN（非 NOT IN），也建议在子查询中排除 NULL 以避免三值逻辑歧义。

最佳实践 ​

  1. 日期区间统一用半开区间 [起, 止)，这是跨越时区、闰秒与精度问题最稳妥的写法。
sql
SELECT * FROM orders
WHERE created_at >= :start AND created_at < :start + interval '1 day';
  1. 反连接一律用 NOT EXISTS，彻底规避 NOT IN 的 NULL 陷阱。
sql
SELECT p.* FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items i WHERE i.product_id = p.id);
  1. IN 列表超过几百项时改成 = ANY(数组) 或临时表连接，避免生成超长的 OR 链。
sql
SELECT * FROM products WHERE id = ANY ($1::int[]);
  1. 参数化的多值查询用数组传入，既安全（无 SQL 注入）又高效。
sql
-- 应用层传 int[] 绑定参数
SELECT * FROM products WHERE category = ANY (:categories);
  1. 使用 ALL 前先判断子查询非空，或改用 > (SELECT max(...)) 这种聚合写法，语义更明确。
sql
-- 更清晰的等价写法
SELECT * FROM products WHERE price > (SELECT max(amount) FROM orders);
  1. 整数/数值区间直接用 BETWEEN，它比 >= AND <= 更简洁且优化器识别一致。
sql
SELECT * FROM products WHERE price BETWEEN 100 AND 500;

  1. 边界可能乱序时用 BETWEEN SYMMETRIC，让 SQL 自己处理顺序，避免应用层额外判断。

  2. 用 EXPLAIN 确认 IN / BETWEEN 走了索引，特别注意子查询是否被改写为半连接。

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM products WHERE price BETWEEN 100 AND 500;

下一节

继续学习：CASE WHEN 表达式

参考链接 ​