PHP小码农

Appearance

WHERE 条件筛选 ​

WHERE 是查询的第一道过滤器，决定了哪些行能够进入后续处理。本节讲解各类比较运算符、逻辑运算符的优先级、IS NULL 判空、BETWEEN 边界、IN / NOT IN 的 NULL 陷阱、EXISTS 初步，以及条件可索引化等关键性能问题。学完本节，你将能写出精确且能走索引的过滤条件。

前置知识

阅读本节前，建议先了解：SELECT 基础

基础概念 ​

WHERE 接收一个布尔表达式（Boolean Expression），对 FROM 产生的每一行求值。其结果是三值逻辑（Three-Valued Logic）：true、false、NULL（未知）。

术语英文说明
谓词Predicate返回真/假/未知的表达式，如 price > 100
选择率Selectivity条件过滤后保留行数占总行数的比例
可索引化Sargable条件能被 B-tree 索引直接利用
全表扫描Seq Scan逐行读取整表，条件无法用索引时的执行方式
部分索引Partial Index只对满足条件的行建索引
相关子查询Correlated Subquery引用外层查询列的子查询

三值逻辑的真值表（AND / OR）：

aba AND ba OR b
truetruetruetrue
truefalsefalsetrue
trueNULLNULLtrue
falsefalsefalsefalse
falseNULLfalseNULL
NULLNULLNULLNULL

关键结论：WHERE 只保留结果为 true 的行，false 与 NULL 都会被丢弃。因此 WHERE price <> 100 会把 price 为 NULL 的行一并排除。

语法与用法 ​

text
SELECT 列列表
FROM 表名
WHERE 布尔表达式
  [AND | OR | NOT 布尔表达式 ...]
ORDER BY 排序键;

准备本节贯穿使用的示例表：

sql
-- 订单表：含可空字段，便于演示 NULL 相关陷阱
CREATE TABLE orders (
  id          INTEGER        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no    TEXT           NOT NULL,
  customer    TEXT           NOT NULL,
  city        TEXT,                                -- 可为 NULL
  amount      NUMERIC(12, 2) NOT NULL CHECK (amount >= 0),
  discount    NUMERIC(12, 2),                      -- 可为 NULL
  status      TEXT           NOT NULL,             -- pending/paid/shipped/closed
  is_paid     BOOLEAN        NOT NULL DEFAULT false,
  created_at  TIMESTAMPTZ    NOT NULL DEFAULT now()
);

INSERT INTO orders (order_no, customer, city, amount, discount, status, is_paid, created_at) VALUES
  ('SO-1001', 'alice', '北京', 1200.00,  100.00, 'paid',    true,  TIMESTAMPTZ '2026-01-05 10:00:00'),
  ('SO-1002', 'bob',   '上海',  300.50,   NULL,  'paid',    true,  TIMESTAMPTZ '2026-01-08 12:30:00'),
  ('SO-1003', 'carol', '广州', 2540.00,  200.00, 'shipped', true,  TIMESTAMPTZ '2026-01-12 09:15:00'),
  ('SO-1004', 'dave',  '北京',  850.00,   NULL,  'pending', false, TIMESTAMPTZ '2026-02-02 16:45:00'),
  ('SO-1005', 'eve',   NULL,    420.75,   50.00, 'paid',    true,  TIMESTAMPTZ '2026-02-09 11:20:00'),
  ('SO-1006', 'frank', '深圳', 9800.00,  500.00, 'closed',  true,  TIMESTAMPTZ '2026-02-15 08:05:00'),
  ('SO-1007', 'alice', '北京',  129.00,   NULL,  'pending', false, TIMESTAMPTZ '2026-02-20 19:40:00'),
  ('SO-1008', 'grace', NULL,    660.00,   60.00, 'shipped', true,  TIMESTAMPTZ '2026-03-01 14:00:00');

最常用的六类条件：

sql
-- 1. 比较运算
SELECT order_no, amount FROM orders WHERE amount > 1000;

-- 2. 逻辑组合
SELECT order_no, amount, status FROM orders
WHERE status = 'paid' AND amount > 500;

-- 3. 集合判断
SELECT order_no, status FROM orders WHERE status IN ('paid', 'shipped');

-- 4. 区间判断
SELECT order_no, amount FROM orders WHERE amount BETWEEN 300 AND 1000;

-- 5. 空值判断
SELECT order_no, city, discount FROM orders WHERE discount IS NULL;

-- 6. 布尔列简写
SELECT order_no, is_paid FROM orders WHERE is_paid;

详细说明 ​

比较运算符 ​

运算符含义示例备注
=等于amount = 100与 NULL 比较恒为 NULL
<>不等于status <> 'paid'标准写法
!=不等于status != 'paid'PostgreSQL 支持，非标准
<小于amount < 1000
<=小于等于amount <= 1000
>大于amount > 1000
>=大于等于amount >= 1000
IS DISTINCT FROMNULL 安全不等于discount IS DISTINCT FROM 0NULL 视为确定值
IS NOT DISTINCT FROMNULL 安全等于a IS NOT DISTINCT FROM b可用于 NULL = NULL
sql
-- 普通比较：NULL 参与恒为 NULL，行被丢弃
SELECT order_no FROM orders WHERE city = '北京';
SELECT order_no FROM orders WHERE city <> '北京';   -- 不含 city 为 NULL 的行！

-- NULL 安全比较：把 NULL 当作一个确定值
SELECT order_no FROM orders WHERE city IS DISTINCT FROM '北京';       -- 含 NULL 行
SELECT order_no FROM orders WHERE city IS NOT DISTINCT FROM NULL;     -- 等价 IS NULL

逻辑运算符优先级与括号 ​

优先级：NOT > AND > OR。混用 AND 与 OR 时务必加括号。

优先级运算符说明
1（高）NOT一元，绑定最紧
2AND二元
3（低）OR二元
sql
-- 危险！实际含义是 status = 'shipped' OR (amount < 500 AND is_paid)
SELECT order_no, status, amount FROM orders
WHERE status = 'shipped' OR amount < 500 AND is_paid;

-- 正确：意图明确
SELECT order_no, status, amount FROM orders
WHERE (status = 'shipped' OR amount < 500) AND is_paid;

NOT 的优先级陷阱同样常见：

sql
-- 危险！NOT 只作用于 is_paid，等价 is_paid = false
SELECT order_no FROM orders WHERE NOT is_paid AND amount > 500;

-- 若本意是"两者都不成立"，需加括号
SELECT order_no FROM orders WHERE NOT (is_paid AND amount > 500);

判空：IS NULL / IS NOT NULL ​

NULL 表示"未知"，不能用 = 判断：

sql
-- 错误！结果永远为 0 行
SELECT order_no, city FROM orders WHERE city = NULL;

-- 正确
SELECT order_no, city FROM orders WHERE city IS NULL;

-- 反向
SELECT order_no, city FROM orders WHERE city IS NOT NULL;

布尔列的判假也推荐用 IS FALSE / IS NOT TRUE，而不是 = false：

sql
-- 推荐：语义精确
SELECT order_no FROM orders WHERE is_paid IS FALSE;

-- 等价但需注意 NULL：is_paid = false 对 NULL 行返回 NULL，会被排除
SELECT order_no FROM orders WHERE is_paid = false;

BETWEEN 边界与日期陷阱 ​

BETWEEN a AND b 等价于 >= a AND <= b，是闭区间（含两端）。日期时间类型最容易踩边界坑：

sql
-- 金额在 300 到 1000 之间（含 300 与 1000）
SELECT order_no, amount FROM orders WHERE amount BETWEEN 300 AND 1000;

-- 等价写法
SELECT order_no, amount FROM orders WHERE amount >= 300 AND amount <= 1000;

-- 日期陷阱：对 timestamptz 用 BETWEEN 会漏掉 12-31 当天 00:00 之后的数据
SELECT order_no, created_at FROM orders
WHERE created_at BETWEEN TIMESTAMPTZ '2026-01-01' AND TIMESTAMPTZ '2026-01-31';   -- 漏数据

-- 推荐：半开区间 [起, 止)
SELECT order_no, created_at FROM orders
WHERE created_at >= TIMESTAMPTZ '2026-01-01'
  AND created_at <  TIMESTAMPTZ '2026-02-01';
text
 order_no |      created_at
----------+-----------------------
 SO-1001  | 2026-01-05 10:00:00+08
 SO-1002  | 2026-01-08 12:30:00+08
 SO-1003  | 2026-01-12 09:15:00+08
 SO-1004  | 2026-02-02 16:45:00+08
(4 rows)
sql
-- NOT BETWEEN：注意 NULL 行同样会被排除
SELECT order_no, amount FROM orders WHERE amount NOT BETWEEN 300 AND 1000;

IN 与 NOT IN 的 NULL 陷阱 ​

IN 是多个 OR 的简写，NOT IN 则是多个 AND <> 的简写，后者在遇到 NULL 时结果会静默变空。

sql
-- IN：枚举值
SELECT order_no, status FROM orders WHERE status IN ('paid', 'shipped');

-- 等价写法
SELECT order_no, status FROM orders
WHERE status = 'paid' OR status = 'shipped';

-- NOT IN 陷阱：列表中含 NULL 时整条语句返回 0 行
SELECT order_no FROM orders WHERE status NOT IN ('paid', NULL);   -- 返回 0 行！

-- 原因：展开为 status <> 'paid' AND status <> NULL，后者恒为 NULL，
--       因此整体结果永远不是 true，任何行都无法通过。

-- 安全写法：子查询中显式排除 NULL
SELECT order_no FROM orders
WHERE city NOT IN (SELECT DISTINCT city FROM orders WHERE city IS NOT NULL);

-- 更推荐：用 NOT EXISTS，天然不受 NULL 影响
SELECT o.order_no FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM (VALUES ('北京')) AS t(city) WHERE t.city = o.city
);

EXISTS 初步 ​

EXISTS 判断子查询是否至少返回一行，返回布尔值。它不关心子查询返回什么列，SELECT 1 是最常见的写法。EXISTS 遇到 NULL 不会出问题，配合 NOT 是最安全的存在性判断：

sql
-- 示例：只保留"存在已付款订单"的客户
SELECT DISTINCT o.customer
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM orders p
  WHERE p.customer = o.customer AND p.is_paid
)
ORDER BY o.customer;

-- NOT EXISTS：从未付款的客户
SELECT DISTINCT o.customer
FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM orders p WHERE p.customer = o.customer AND p.is_paid
)
ORDER BY o.customer;
text
 customer
----------
 alice
 bob
 carol
 dave
 eve
 frank
 grace
(7 rows)

EXISTS 与 IN 的对比：

对比项INEXISTS
返回值值集合成员判断是否存在行
遇到 NULLNOT IN 会静默变空不受影响
子查询列必须返回单列返回什么都行
典型优化哈希半连接半连接（Semi Join）
推荐场景固定枚举值列表存在性判断、大子查询

EXISTS 也可用于"存在性"的近乎重复，但注意它的语义是"至少一行"，不保证唯一。

布尔列简写 ​

boolean 列本身就是布尔表达式，可直接作为条件：

sql
-- 简洁写法
SELECT order_no FROM orders WHERE is_paid;

-- 等价写法
SELECT order_no FROM orders WHERE is_paid = true;

-- 取反
SELECT order_no FROM orders WHERE NOT is_paid;

-- 三态判断
SELECT order_no FROM orders WHERE is_paid IS TRUE;
SELECT order_no FROM orders WHERE is_paid IS NOT TRUE;   -- 含 false 与 NULL

条件索引失效的原因 ​

在 WHERE 中对列做运算或函数调用，会导致 B-tree 索引无法使用，这类条件称为不可索引化（Non-Sargable）。

写法是否可索引原因
city = '北京'可列在比较一侧，纯值引用
amount > 1000可范围条件可用 B-tree
upper(city) = 'BEIJING'不可列被函数包裹
amount * 1.06 > 1000不可列参与运算
created_at::date = '2026-01-05'不可列被显式转换
city LIKE '%京'不可前导通配符
city LIKE '北%'可前缀匹配可用 B-tree（需 text_pattern_ops 或 C locale）
sql
-- 建索引后对比
CREATE INDEX idx_orders_city ON orders (city);
CREATE INDEX idx_orders_created_at ON orders (created_at);

-- 错误！函数包裹列，索引失效，走 Seq Scan
SELECT order_no FROM orders WHERE upper(city) = '北京';

-- 方案一：把函数作用到常量侧
SELECT order_no FROM orders WHERE city = '北京';

-- 方案二：创建表达式索引
CREATE INDEX idx_orders_city_upper ON orders (upper(city));
SELECT order_no FROM orders WHERE upper(city) = '北京';   -- 可用表达式索引

-- 错误！对时间列做 ::date 转换，索引失效
SELECT order_no FROM orders WHERE created_at::date = DATE '2026-01-05';

-- 正确：改成半开区间
SELECT order_no FROM orders
WHERE created_at >= TIMESTAMPTZ '2026-01-05'
  AND created_at <  TIMESTAMPTZ '2026-01-06';

WHERE 与 HAVING 的区别 ​

对比项WHEREHAVING
作用对象原始行分组后的组
执行时机分组之前分组之后
能否用聚合函数不能能
能否用 SELECT 别名不能可以（PostgreSQL 允许）
性能提前过滤、减少聚合量无法减少聚合计算量
sql
-- 错误！WHERE 中不能用聚合函数
SELECT city, count(*) FROM orders
WHERE count(*) > 1
GROUP BY city;

-- 正确：聚合条件放 HAVING
SELECT city, count(*) AS order_cnt FROM orders
WHERE is_paid                       -- 行级过滤留在 WHERE
GROUP BY city
HAVING count(*) > 1                 -- 组级过滤放 HAVING
ORDER BY order_cnt DESC;

-- 优化：能把条件提前到 WHERE 的一律提前
-- 差：先分组再筛掉 status <> 'paid' 的组
SELECT customer, sum(amount) FROM orders GROUP BY customer HAVING min(status) = 'paid';

-- 好：先过滤行，再分组
SELECT customer, sum(amount) FROM orders WHERE status = 'paid' GROUP BY customer;

实战示例 ​

下面用一段完整的"订单筛选与条件优化"流程，串联本节要点。

sql
-- 步骤 1：多条件组合筛选（注意括号）
SELECT order_no, customer, city, amount, status
FROM orders
WHERE is_paid
  AND status IN ('paid', 'shipped')
  AND amount BETWEEN 300 AND 3000
  AND (city = '北京' OR city = '上海')
ORDER BY amount DESC;
text
 order_no | customer | city |  amount  | status
----------+----------+------+----------+---------
 SO-1001  | alice    | 北京 |  1200.00 | paid
 SO-1002  | bob      | 上海 |   300.50 | paid
(2 rows)

SO-1004 的 is_paid 为 false，因此被排除。

sql
-- 步骤 2：判空条件组合，找出"未填城市且无折扣"的订单
SELECT order_no, customer, city, discount
FROM orders
WHERE city IS NULL
  AND discount IS NULL;
text
 order_no | customer | city | discount
----------+----------+------+----------
(0 rows)
sql
-- 步骤 3：EXISTS 做存在性判断——有过大额订单的客户
SELECT DISTINCT o.customer, o.city
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM orders x
  WHERE x.customer = o.customer AND x.amount >= 1000
)
ORDER BY o.customer;
text
 customer | city
----------+------
 alice    | 北京
 carol    | 广州
 frank    | 深圳
(3 rows)
sql
-- 步骤 4：NOT IN 陷阱演示与修正
-- 错误！返回 0 行（因为 LEFT JOIN 产生 NULL）
SELECT order_no, customer FROM orders
WHERE customer NOT IN (SELECT city FROM orders);
-- 结果：0 行（city 列含 NULL，导致整体恒为 NULL）

-- 正确：排除 NULL
SELECT order_no, customer FROM orders
WHERE customer NOT IN (SELECT city FROM orders WHERE city IS NOT NULL);
text
 order_no | customer
----------+----------
 SO-1001  | alice
 SO-1002  | bob
 SO-1003  | carol
 SO-1004  | dave
 SO-1005  | eve
 SO-1006  | frank
 SO-1007  | alice
 SO-1008  | grace
(8 rows)
sql
-- 步骤 5：验证条件是否走索引
CREATE INDEX idx_orders_is_paid_amount ON orders (is_paid, amount);

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_no, amount FROM orders
WHERE is_paid AND amount > 500;
text
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Index Scan using idx_orders_is_paid_amount on orders  (cost=0.14..8.16 rows=1 width=40)
                                                        (actual time=0.021..0.023 rows=3 loops=1)
   Index Cond: ((is_paid = true) AND (amount > '500'::numeric))
   Buffers: shared hit=4
 Planning Time: 0.118 ms
 Execution Time: 0.041 ms
(5 rows)
sql
-- 步骤 6：对比不可索引化写法（走 Seq Scan）
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_no, amount FROM orders
WHERE is_paid AND amount * 1.06 > 530;      -- 列参与运算，索引失效
text
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..1.10 rows=1 width=40) (actual time=0.011..0.013 rows=3 loops=1)
   Filter: (is_paid AND ((amount * '1.06'::numeric) > '530'::numeric))
   Rows Removed by Filter: 5
   Buffers: shared hit=1
 Planning Time: 0.092 ms
 Execution Time: 0.029 ms
(6 rows)

小表上两种写法差异不明显，但数据量增长后 Seq Scan 会成为瓶颈。

sql
-- 步骤 7：部分索引加速高频条件的等值查询
CREATE INDEX idx_orders_pending_customer ON orders (customer) WHERE is_paid = false;

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_no, customer FROM orders WHERE is_paid = false AND customer = 'alice';
text
                                      QUERY PLAN
----------------------------------------------------------------------------------------
 Index Scan using idx_orders_pending_customer on orders  (cost=0.13..8.14 rows=1 width=40)
                                                          (actual time=0.012..0.013 rows=1 loops=1)
   Index Cond: (customer = 'alice'::text)
   Buffers: shared hit=3
 Planning Time: 0.105 ms
 Execution Time: 0.022 ms
(5 rows)

注意事项 ​

  • NOT IN 遇到 NULL 会静默返回空结果，这是最隐蔽的一类查询错误。子查询排除一律优先 NOT EXISTS。
sql
-- 错误！子查询含 NULL 时整条语句返回 0 行
SELECT order_no FROM orders WHERE city NOT IN (SELECT city FROM orders WHERE city IS NULL);

-- 正确
SELECT order_no FROM orders WHERE city IS NOT NULL;
  • AND 优先级高于 OR，混用时务必加括号。NOT 的绑定范围也容易误判。
sql
-- 危险：NOT 只作用于 is_paid
SELECT order_no FROM orders WHERE NOT is_paid AND amount > 500;

-- 正确：意图明确
SELECT order_no FROM orders WHERE NOT (is_paid AND amount > 500);
  • = NULL 永远不成立。判空必须用 IS NULL / IS NOT NULL。
  • <>, != 会排除 NULL 行。若需要把 NULL 视为独立值参与比较，使用 IS DISTINCT FROM。
sql
-- 不含 city 为 NULL 的行
SELECT order_no FROM orders WHERE city <> '北京';

-- 含 city 为 NULL 的行
SELECT order_no FROM orders WHERE city IS DISTINCT FROM '北京';
  • BETWEEN 用在时间列上注意边界。对 timestamptz 用 BETWEEN '2026-01-01' AND '2026-01-31' 会漏掉 1 月 31 日当天数据，推荐半开区间。
  • 在 WHERE 中对列做函数或运算会导致索引失效。这是最常见的性能问题之一，改写为常量侧运算或创建表达式索引。
  • LIKE '%xx' 无法使用 B-tree 索引。逆序匹配需求可考虑 reverse() 表达式索引，或用 pg_trgm 的 GIN 索引。
  • OR 条件可能削弱索引效果。多个 OR 分支若各自有索引，优化器可能用 BitmapOr，但分支过多时仍会退化为全表扫描；可改写为 UNION ALL 或 IN。
  • 隐式类型转换会阻止索引使用。列类型与常量类型不一致时，PostgreSQL 可能对列做转换。
sql
-- 错误！uuid 列与 text 常量比较，可能触发隐式转换
SELECT * FROM orders WHERE id = '1';

-- 正确：显式匹配类型
SELECT * FROM orders WHERE id = 1;
  • WHERE 条件的书写顺序不影响性能。优化器会自行重排，不要为"性能"刻意调整顺序，应按业务逻辑分组并加括号提升可读性。

最佳实践 ​

  1. 判空统一用 IS NULL / IS NOT NULL，存在性判断统一用 EXISTS / NOT EXISTS，彻底规避 NULL 陷阱。
  2. 时间范围统一用半开区间 >= 起 AND < 止，语义清晰且不依赖列类型。
sql
-- 推荐模板
SELECT * FROM orders
WHERE created_at >= TIMESTAMPTZ '2026-01-01'
  AND created_at <  TIMESTAMPTZ '2026-02-01';
  1. 保持条件"可索引化"，这是 WHERE 性能的第一原则。列上不做任何函数或运算。
sql
-- 不推荐
SELECT * FROM orders WHERE extract(year FROM created_at) = 2026;

-- 推荐
SELECT * FROM orders
WHERE created_at >= TIMESTAMPTZ '2026-01-01' AND created_at < TIMESTAMPTZ '2027-01-01';
  1. 为高频过滤组合建复合索引，遵循"等值列在前、范围列在后"的原则。
sql
-- 支撑 WHERE is_paid AND city = ? AND amount > ?
CREATE INDEX idx_orders_paid_city_amount ON orders (is_paid, city, amount);
  1. 高频小范围查询可用部分索引，体积小、更新代价低。
sql
-- 只索引未付款订单（通常占比很小）
CREATE INDEX idx_orders_unpaid ON orders (customer, created_at) WHERE is_paid = false;
  1. EXISTS 优于 IN 做子查询存在性判断，尤其当子查询结果可能含 NULL 或数据量大时。
sql
-- 推荐
SELECT DISTINCT o.customer FROM orders o
WHERE EXISTS (SELECT 1 FROM orders x WHERE x.customer = o.customer AND x.amount > 5000);
  1. 用 EXPLAIN (ANALYZE, BUFFERS) 验证条件是否走索引。看到 Seq Scan 且 Rows Removed by Filter 很大，说明索引缺失或条件不可索引化。
sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_no, amount FROM orders WHERE status = 'paid' AND amount > 500;
  1. 能提前到 WHERE 的条件不要放在 HAVING。先减少行数再分组聚合，代价更低。

下一节

继续学习：排序（ORDER BY）

参考链接 ​