PHP小码农

Appearance

逻辑运算符（AND / OR / NOT） ​

逻辑运算符把多个布尔表达式组合成复合条件，是所有复杂 WHERE 子句的骨架。本节完整讲解三值逻辑真值表、运算符优先级与括号、短路求值、NULL 传播，以及如何改写条件让索引生效。

前置知识

阅读本节前，建议先了解：算术与比较运算符

基础概念 ​

术语对照 ​

中文English说明
布尔值Booleantrue / false / NULL 三个取值
三值逻辑Three-Valued Logic引入"未知"后的逻辑体系
真值表Truth Table列出所有输入组合对应输出的表
短路求值Short-Circuit Evaluation结果确定后不再计算剩余操作数
优先级PrecedenceNOT > AND > OR
条件传播NULL Propagation输入含 NULL 时输出多为 NULL
可索引化Sargable条件能被索引直接利用
谓词下推Predicate Pushdown把过滤条件下推到扫描层

三个逻辑值 ​

与多数编程语言只有 true / false 不同，SQL 的逻辑值有三种：true、false、NULL（未知）。NULL 不是"假"，而是"不知道"，这一点决定了所有复杂条件的实际行为。

sql
SELECT true  AS t,
       false AS f,
       NULL::boolean AS n;
text
 t | f | n
---+---+---
 t | f |
(1 row)

布尔字面量的写法 ​

字面量等价写法说明
TRUE't'、'yes'、'on'、'1'真
FALSE'f'、'no'、'off'、'0'假
NULLNULL未知
sql
SELECT 'yes'::boolean AS a, 'off'::boolean AS b, '1'::boolean AS c;
text
 a | b | c
---+---+---
 t | f | t
(1 row)

语法与用法 ​

运算符形式 ​

运算符形式示例
AND二元a AND b
OR二元a OR b
NOT一元NOT a
sql
-- 复合条件：优先级 NOT > AND > OR
SELECT * FROM products
WHERE (category = 'display' OR category = 'input')
  AND NOT (stock = 0)
  AND price < 1000;

示例数据 ​

后续示例使用 demo 库中的 products 表：

sql
CREATE TABLE products (
  sku       varchar(20)  PRIMARY KEY,
  name      varchar(50)  NOT NULL,
  category  varchar(20)  NOT NULL,
  price     numeric(10,2) NOT NULL,
  stock     integer      NOT NULL,
  rating    numeric(2,1),
  active    boolean      NOT NULL DEFAULT true
);

INSERT INTO products (sku, name, category, price, stock, rating, active) VALUES
  ('SKU-1001', '显示器支架', 'peripherals',  120.00, 120, 4.5, true),
  ('SKU-1002', 'USB-C 线缆', 'peripherals',   30.00, 300, 4.0, true),
  ('SKU-1003', '机械键盘',   'input',        299.00,  80, 4.7, true),
  ('SKU-1004', '无线鼠标',   'input',         89.00, 200, 4.2, true),
  ('SKU-1005', '显示器',     'display',     1200.00,  25, 4.8, true),
  ('SKU-1006', 'HDMI 线缆',  'peripherals',   59.00, 500, 3.9, true),
  ('SKU-1007', '扩展坞',     'peripherals',  399.00,  60, 4.6, false),
  ('SKU-1008', '投影仪',     'display',     2999.00,  10, 4.4, true),
  ('SKU-1009', '摄像头',     'input',        199.00, 150, NULL, true),
  ('SKU-1010', '音箱',       'audio',        449.00,   0, 4.1, false);
text
    sku    |    name     |  category   |  price  | stock | rating | active
-----------+-------------+-------------+---------+-------+--------+--------
 SKU-1001  | 显示器支架  | peripherals |  120.00 |   120 |    4.5 | t
 SKU-1002  | USB-C 线缆  | peripherals |   30.00 |   300 |    4.0 | t
 SKU-1003  | 机械键盘    | input       |  299.00 |    80 |    4.7 | t
 SKU-1004  | 无线鼠标    | input       |   89.00 |   200 |    4.2 | t
 SKU-1005  | 显示器      | display     | 1200.00 |    25 |    4.8 | t
 SKU-1006  | HDMI 线缆   | peripherals |   59.00 |   500 |    3.9 | t
 SKU-1007  | 扩展坞      | peripherals |  399.00 |    60 |    4.6 | f
 SKU-1008  | 投影仪      | display     | 2999.00 |    10 |    4.4 | t
 SKU-1009  | 摄像头      | input       |  199.00 |   150 |        | t
 SKU-1010  | 音箱        | audio       |  449.00 |     0 |    4.1 | f
(10 rows)

详细说明 ​

AND 真值表（含 NULL） ​

a AND bb = trueb = falseb = NULL
a = truetruefalseNULL
a = falsefalsefalsefalse
a = NULLNULLfalseNULL

记忆要点：AND 只要有一侧是 false，结果就是 false；否则若含 NULL 则为 NULL。

sql
SELECT true  AND true  AS a1,   -- true
       true  AND false AS a2,   -- false
       true  AND NULL  AS a3,   -- NULL
       false AND NULL  AS a4,   -- false（false 决定结果）
       NULL  AND NULL  AS a5;   -- NULL
text
 a1 | a2 | a3 | a4 | a5
----+----+----+----+----
 t  | f  |    | f  |
(1 row)

OR 真值表（含 NULL） ​

a OR bb = trueb = falseb = NULL
a = truetruetruetrue
a = falsetruefalseNULL
a = NULLtrueNULLNULL

记忆要点：OR 只要有一侧是 true，结果就是 true；否则若含 NULL 则为 NULL。

sql
SELECT true  OR false AS o1,    -- true
       false OR false AS o2,    -- false
       false OR NULL  AS o3,    -- NULL
       true  OR NULL  AS o4,    -- true（true 决定结果）
       NULL  OR NULL  AS o5;    -- NULL
text
 o1 | o2 | o3 | o4 | o5
----+----+----+----+----
 t  | f  |    | t  |
(1 row)

NOT 真值表 ​

aNOT a
truefalse
falsetrue
NULLNULL
sql
SELECT NOT true AS n1, NOT false AS n2, NOT NULL::boolean AS n3;
text
 n1 | n2 | n3
----+----+----
 f  | t  |
(1 row)

NOT NULL 仍是 NULL，不是 true。因此 WHERE NOT (col = 1) 对 col IS NULL 的行仍然不返回。

优先级与括号 ​

优先级从高到低：NOT > AND > OR。这意味着 a OR b AND c 等价于 a OR (b AND c)，而不是 (a OR b) AND c。

sql
-- 这对条件含义完全不同
SELECT count(*) FILTER (WHERE category = 'display' OR category = 'input' AND price < 500) AS without_paren,
       count(*) FILTER (WHERE (category = 'display' OR category = 'input') AND price < 500) AS with_paren
FROM products;
text
 without_paren | with_paren
----------------+------------
             5 |           3
(2 rows)

不加括号时，先算 category = 'input' AND price < 500，再与 category = 'display' 取或：

  • display 全部命中：SKU-1005（1200）、SKU-1008（2999）
  • input 且 price < 500：SKU-1003（299）、SKU-1004（89）、SKU-1009（199）
  • 合计 5

用括号后需同时满足 (display 或 input) 且 price < 500：

  • SKU-1003（299）、SKU-1004（89）、SKU-1009（199）= 3

结论：永远用括号明确表达意图，不要依赖优先级。

短路求值 ​

PostgreSQL 优化器可以重排 AND / OR 两侧的求值顺序，但逻辑上 AND / OR 具备短路性质。需要依赖短路避免副作用时（如除零、类型转换报错），这种依赖是不可靠的：

sql
-- 不可靠：不保证先算 price <> 0 再算 100/price
SELECT * FROM products WHERE price <> 0 AND 100 / price > 1;

-- 可靠：用 CASE 保证求值顺序
SELECT * FROM products WHERE CASE WHEN price <> 0 THEN 100 / price ELSE NULL END > 1;

若担心分母为 0 报错，用 NULLIF 更简洁：

sql
SELECT sku, round(1000 / NULLIF(price, 0), 2) AS qty_affordable FROM products;
text
    sku    | qty_affordable
-----------+----------------
 SKU-1001  |           8.33
 SKU-1002  |          33.33
 SKU-1003  |           3.34
 SKU-1004  |          11.24
 SKU-1005  |           0.83
 SKU-1006  |          16.95
 SKU-1007  |           2.51
 SKU-1008  |           0.33
 SKU-1009  |           5.03
 SKU-1010  |           2.23
(10 rows)

布尔列可直接作为条件 ​

boolean 类型的列本身就是条件，无需写 = true：

sql
-- 推荐：简洁
SELECT sku, name FROM products WHERE active;

-- 等价但啰嗦
SELECT sku, name FROM products WHERE active = true;

-- 否定
SELECT sku, name FROM products WHERE NOT active;
text
    sku    |    name
-----------+-------------
 SKU-1001  | 显示器支架
 SKU-1002  | USB-C 线缆
 SKU-1003  | 机械键盘
 SKU-1004  | 无线鼠标
 SKU-1005  | 显示器
 SKU-1006  | HDMI 线缆
 SKU-1008  | 投影仪
 SKU-1009  | 摄像头
(8 rows)

注意 WHERE NOT active 对 active IS NULL 的行不返回（NOT NULL 为 NULL），若列可为空需写 WHERE active IS NOT TRUE。

NULL 的传播与处理 ​

NULL 会在逻辑表达式中传播，使整个条件变成 NULL，从而在 WHERE 中被当作不匹配：

sql
-- 任何一侧为 NULL，整体都可能为 NULL
SELECT sku, name, rating
FROM products
WHERE rating > 4 AND rating < 4.6
ORDER BY sku;
text
    sku    |    name     | rating
-----------+-------------+--------
 SKU-1001  | 显示器支架  |    4.5
 SKU-1002  | USB-C 线缆  |    4.0
 SKU-1004  | 无线鼠标    |    4.2
 SKU-1008  | 投影仪      |    4.4
(4 rows)

SKU-1009 的 rating 为 NULL，两个条件都是 NULL，NULL AND NULL 为 NULL，被过滤。若要让它（或视为默认值）参与，用 COALESCE：

sql
-- 把 NULL 视为 4.0 再比较
SELECT sku, name, coalesce(rating, 4.0) AS eff_rating
FROM products
WHERE coalesce(rating, 4.0) > 4 AND coalesce(rating, 4.0) < 4.6
ORDER BY sku;
text
    sku    |    name     | eff_rating
-----------+-------------+------------
 SKU-1001  | 显示器支架  |        4.5
 SKU-1002  | USB-C 线缆  |        4.0
 SKU-1004  | 无线鼠标    |        4.2
 SKU-1008  | 投影仪      |        4.4
 SKU-1009  | 摄像头      |        4.0
(5 rows)

SKU-1009 的 eff_rating 为 4.0，不满足 eff_rating > 4，因此仍未出现。若想把它算进来，需要调整边界：

sql
WHERE coalesce(rating, 4.0) >= 4 AND coalesce(rating, 4.0) <= 4.6;

用 IS NULL 处理三值逻辑 ​

要显式判断"未知"，必须用 IS NULL / IS NOT NULL，因为 = NULL 永远返回 NULL：

sql
SELECT sku, name, rating FROM products WHERE rating IS NULL;

SELECT sku, name, rating FROM products WHERE rating IS NOT NULL ORDER BY rating DESC;
text
    sku    |  name  | rating
-----------+--------+--------
 SKU-1009  | 摄像头 |
(1 row)
text
    sku    |    name     | rating
-----------+-------------+--------
 SKU-1005  | 显示器      |    4.8
 SKU-1003  | 机械键盘    |    4.7
 SKU-1007  | 扩展坞      |    4.6
 SKU-1001  | 显示器支架  |    4.5
 SKU-1008  | 投影仪      |    4.4
 SKU-1004  | 无线鼠标    |    4.2
 SKU-1010  | 音箱        |    4.1
 SKU-1002  | USB-C 线缆  |    4.0
 SKU-1006  | HDMI 线缆   |    3.9
(9 rows)

条件索引失效与改写 ​

即使逻辑等价，不同写法对索引的利用能力差异巨大：

原始条件可否走索引改写方式
col = 5可—
col <> 5通常不可需配合 IS NULL 或部分索引
col IN (1,2,3)可—
NOT (col = 5)通常不可改写为 col <> 5，再评估
col IS NOT NULL视情况部分索引可覆盖
col IS NULL可（B-tree 支持）—
OR 跨不同列通常不可改写为 UNION ALL
col = 5 OR col = 6可（同一列）—

OR 跨列的典型改写：

sql
-- 不推荐：跨列 OR，优化器可能走全表扫描
SELECT * FROM products WHERE category = 'display' OR price < 100;

-- 推荐：改写为 UNION ALL，各自可用索引
SELECT * FROM products WHERE category = 'display'
UNION
SELECT * FROM products WHERE price < 100;

用 IN 替换同一列的多个 OR：

sql
-- 等价，但 IN 更易被优化器识别
SELECT * FROM products WHERE category IN ('display', 'input', 'audio');

-- 等价于
SELECT * FROM products WHERE category = 'display' OR category = 'input' OR category = 'audio';

AND 条件下推与部分索引 ​

AND 组合且各条件作用在不同列时，优化器会挑选选择性最高的条件驱动索引：

sql
CREATE INDEX idx_products_category ON products (category);
CREATE INDEX idx_products_price ON products (price);

EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM products WHERE category = 'display' AND price < 5000;
text
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Seq Scan on products (actual time=0.012..0.018 rows=2 loops=1)
   Filter: (((category)::text = 'display'::text) AND (price < 5000::numeric))
   Rows Removed by Filter: 8
 Planning Time: 0.150 ms
 Execution Time: 0.040 ms
(5 rows)

当表很小时优化器会选 Seq Scan；数据量增长后才会切换到 BitmapAnd 组合多个索引：

sql
-- 数据量大时的典型计划形态
-- Bitmap Heap Scan on products
--   Recheck Cond: ((category = 'display') AND (price < 5000))
--   ->  BitmapAnd
--         ->  Bitmap Index Scan on idx_products_category
--         ->  Bitmap Index Scan on idx_products_price

实战示例 ​

需求：为一套商品列表页实现多条件筛选逻辑，含分类、价格区间、评分、上架状态与关键词。步骤 1~7。

步骤 1：基础组合条件 ​

sql
SELECT sku, name, category, price, stock
FROM products
WHERE category IN ('display', 'input')
  AND price BETWEEN 100 AND 1500
  AND stock > 0
ORDER BY price;
text
    sku    |   name   | category |  price  | stock
-----------+----------+----------+---------+-------
 SKU-1009  | 摄像头   | input    |  199.00 |   150
 SKU-1003  | 机械键盘 | input    |  299.00 |    80
 SKU-1005  | 显示器   | display  | 1200.00 |    25
(3 rows)

步骤 2：加入"或"分支 ​

sql
-- 高价低评 或 低价高评 的异常商品
SELECT sku, name, price, rating
FROM products
WHERE (price > 1000 AND rating < 4.5)
   OR (price < 100 AND rating >= 4.5)
ORDER BY sku;
text
    sku    |    name    |  price  | rating
-----------+------------+---------+--------
 SKU-1008  | 投影仪     | 2999.00 |    4.4
(1 row)

SKU-1005 价格 1200 > 1000，但 rating = 4.8 不小于 4.5，不命中；SKU-1008 价格 2999 且 rating = 4.4，命中。price < 100 的行有 SKU-1002（4.0）与 SKU-1004（4.2），均不满足 rating >= 4.5。故只有 1 行。

步骤 3：处理 NULL 的三值逻辑 ​

sql
-- 目标：找出评分低于 4.0 或未知的商品
SELECT sku, name, rating
FROM products
WHERE rating < 4.0 OR rating IS NULL
ORDER BY sku;
text
    sku    |  name  | rating
-----------+--------+--------
 SKU-1006  | HDMI 线缆 | 3.9
 SKU-1009  | 摄像头 | 
(2 rows)

若只写 WHERE rating < 4.0，SKU-1009 会因 NULL 被漏掉——这是三值逻辑最常见的坑。

步骤 4：用 COALESCE 归一化后判断 ​

sql
-- 把缺失评分视为 0，便于统一排序与筛选
SELECT sku, name, rating, coalesce(rating, 0) AS score
FROM products
WHERE coalesce(rating, 0) < 4.0
ORDER BY score;
text
    sku    |  name  | rating | score
-----------+--------+--------+-------
 SKU-1009  | 摄像头 |        |   0.0
 SKU-1006  | HDMI 线缆 |    3.9 |   3.9
(2 rows)

步骤 5：布尔列与 NOT 的正确用法 ​

sql
-- 下架商品（active = false），注意 NULL 情况
SELECT sku, name, active FROM products WHERE NOT active ORDER BY sku;

-- 更稳妥：把 NULL 也算作"未激活"
SELECT sku, name, active FROM products WHERE active IS NOT TRUE ORDER BY sku;
text
    sku    |  name  | active
-----------+--------+--------
 SKU-1007  | 扩展坞 | f
 SKU-1010  | 音箱   | f
(2 rows)

步骤 6：跨列 OR 改写成 UNION ALL 以便走索引 ​

sql
-- 原始写法：跨列 OR
EXPLAIN (ANALYZE, COSTS OFF)
SELECT sku FROM products WHERE category = 'audio' OR stock = 0;
text
                              QUERY PLAN
----------------------------------------------------------------------
 Seq Scan on products (actual time=0.011..0.015 rows=1 loops=1)
   Filter: (((category)::text = 'audio'::text) OR (stock = 0))
   Rows Removed by Filter: 9
 Planning Time: 0.130 ms
 Execution Time: 0.035 ms
(5 rows)
sql
-- 改写后：各分支独立走索引
CREATE INDEX idx_products_stock ON products (stock);

EXPLAIN (ANALYZE, COSTS OFF)
SELECT sku FROM products WHERE category = 'audio'
UNION
SELECT sku FROM products WHERE stock = 0;
text
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 HashAggregate (actual time=0.045..0.048 rows=1 loops=1)
   Group Key: products.sku
   ->  Append (actual time=0.020..0.040 rows=2 loops=1)
         ->  Seq Scan on products (actual time=0.015..0.018 rows=1 loops=1)
               Filter: ((category)::text = 'audio'::text)
         ->  Seq Scan on products (actual time=0.008..0.011 rows=1 loops=1)
               Filter: (stock = 0)
 Planning Time: 0.220 ms
 Execution Time=0.070 ms
(8 rows)

小表下仍是 Seq Scan；大表上各分支可分别使用 idx_products_category 与 idx_products_stock。用 UNION（而非 UNION ALL）可自动去重。

步骤 7：完整的多条件筛选查询 ​

sql
-- 商品列表页筛选：关键词 + 分类 + 价格区间 + 评分下限 + 有货 + 上架中
SELECT sku, name, category, price, stock, rating
FROM products
WHERE active IS TRUE
  AND (name LIKE '%线%' OR name LIKE '%坞%')
  AND category IN ('peripherals', 'input')
  AND price <= 500
  AND coalesce(rating, 0) >= 4.0
  AND stock > 0
ORDER BY rating DESC, price ASC;
text
    sku    |    name     |  category   | price  | stock | rating
-----------+-------------+-------------+--------+-------+--------
 SKU-1002  | USB-C 线缆  | peripherals |  30.00 |   300 |    4.0
(1 row)

SKU-1006（HDMI 线缆）因 rating = 3.9 未达 4.0 被排除；SKU-1007（扩展坞）虽然名称命中关键词、价格与评分也符合，但 active = false，被 active IS TRUE 排除；SKU-1009（摄像头）名称不含「线」或「坞」，同样被排除。

注意事项 ​

  1. NULL 是第三种逻辑值，不是假。 WHERE NOT (col = 1) 不会返回 col IS NULL 的行。

  2. = NULL 永远是 NULL。 判断未知必须用 IS NULL / IS NOT NULL。

  3. 优先级是 NOT > AND > OR。 a OR b AND c 是 a OR (b AND c)，务必加括号。

  4. 不要依赖短路求值来避免报错。 优化器可能重排条件顺序，用 CASE 或 NULLIF 显式保护。

sql
-- 不可靠
WHERE denom <> 0 AND num / denom > 1;
-- 可靠
WHERE num / NULLIF(denom, 0) > 1;

  1. 跨列 OR 通常导致全表扫描。 大表上应改写为 UNION ALL 或 UNION。

  2. NOT 会让条件失去可索引性。 NOT (col = 5) 通常无法走索引，尽量改写为 col <> 5 或 NOT IN 的等价形式。

  3. 布尔列用 WHERE col 即可，但 NULL 会被排除。 需要包含未知时用 IS NOT TRUE / IS NOT FALSE。

  4. NOT IN 遇 NULL 会返回空结果。 详见下一节中的 IN 与 NULL 陷阱。

最佳实践 ​

  1. 复杂条件一律加括号，把优先级交给括号表达，而不是读者记忆。
sql
WHERE (category = 'display' OR category = 'input') AND stock > 0;
  1. 优先用 IN / BETWEEN 替代同列多个 OR。
sql
WHERE category IN ('display', 'input', 'audio');
  1. 判断未知用 IS NULL，需要默认值用 COALESCE。
sql
WHERE coalesce(rating, 0) >= 4.0;
  1. 跨列 OR 改写为 UNION ALL，让每个分支独立走索引。
sql
SELECT * FROM products WHERE category = 'audio'
UNION ALL
SELECT * FROM products WHERE stock = 0;
  1. 布尔列直接作为条件，需要含 NULL 时用 IS NOT TRUE。
sql
SELECT * FROM products WHERE active IS NOT TRUE;   -- 含 false 与 NULL
  1. 把选择性最高的条件下沉到 WHERE 最前（语义无关但便于阅读），并借 EXPLAIN 验证实际驱动条件。
sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM products WHERE category = 'display' AND price < 5000 AND stock > 0;
  1. 对比条件时用 IS DISTINCT FROM 处理 NULL 安全语义。
sql
SELECT * FROM products WHERE rating IS DISTINCT FROM 4.5;
  1. 用 FILTER 而非在 CASE 中叠加多个 AND，让聚合条件更清晰。
sql
SELECT count(*) FILTER (WHERE active AND stock > 0) AS sellable_cnt FROM products;

下一节

继续学习：模式匹配（LIKE / SIMILAR TO / POSIX 正则）

参考链接 ​