4 WHERE

WHERE 用于过滤行。这个子句位于 FROM 与 ORDER BY 之间，PostgreSQL 只保留条件求值为真的行。结果为假或 NULL 的行都会被排除。这种三值逻辑是 SQL 的一个特别之处，也是许多初学者出错的根源，最好尽早熟悉它。

本章要点

• 用比较操作符（=、<>、<、>、<=、>=）过滤
• 用 LIKE 做模式匹配（不区分大小写时用 ILIKE）
• 用 AND、OR、NOT 组合条件
• 成员判断与范围：IN、BETWEEN
• 为什么 = NULL 不起作用，以及该用什么替代

示例

-- Equality and ordering
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname = 'pg_catalog'
ORDER BY tablename
LIMIT 3;

-- Pattern matching: % matches any sequence, _ matches one character
SELECT tablename
FROM pg_tables
WHERE tablename LIKE 'pg_a%'
ORDER BY tablename
LIMIT 5;

-- Case-insensitive: ILIKE (PostgreSQL extension)
SELECT tablename FROM pg_tables WHERE tablename ILIKE 'PG_Class' LIMIT 1;

-- Multiple conditions
SELECT tablename
FROM pg_tables
WHERE schemaname = 'pg_catalog'
  AND tablename LIKE 'pg_%'
  AND tablename NOT LIKE 'pg_stat_%'
ORDER BY tablename
LIMIT 5;

-- IN: membership test
SELECT tablename
FROM pg_tables
WHERE tablename IN ('pg_class', 'pg_database', 'pg_namespace')
ORDER BY tablename;

-- BETWEEN: inclusive range
SELECT n FROM generate_series(1, 10) AS n WHERE n BETWEEN 3 AND 7;

LIKE 是 SQL 标准中的模式匹配操作符：% 匹配任意字符串（包括空字符串），_ 恰好匹配一个字符。ILIKE 是 PostgreSQL 提供的扩展，用于不区分大小写的匹配。能否使用索引取决于匹配模式、排序规则和操作符类。字母前缀通常需要合适的表达式索引或三字符组（trigram）索引；对于以不受大小写影响的字符开头的某些匹配模式，B-树也能提供支持。若需要更强大的匹配能力，PostgreSQL 还支持 ~（正则表达式）、~*（不区分大小写的正则表达式），以及后面会介绍的全文检索。

AND 的优先级高于 OR，所以 A AND B OR C 的含义是 (A AND B) OR C。拿不准时就加括号，日后再读这条查询时，你会感谢现在的自己。IN (...) 是 = ANY (ARRAY[...]) 的简写，等价于一串用 OR 连接的条件。

运行示例

运行方式：

$ psql -f source/where.sql postgres
 schemaname |  tablename
------------+--------------
 pg_catalog | pg_aggregate
 pg_catalog | pg_am
 pg_catalog | pg_amop
(3 rows)
 ...

常见陷阱

• = NULL 和 <> NULL 永远不会为真（结果是 NULL）。请用 IS NULL 和 IS NOT NULL。从其他各种语言转来的工程师都容易在这里栽跟头。
• 如果子查询包含 NULL，NOT IN (subquery) 的行为就会变得难以理解：不匹配的值会得到 NULL，匹配的值会得到假，因此过滤后不会保留任何行。对于可能返回空值的子查询，优先使用 NOT EXISTS。
• LIKE 'abc' 不会匹配 'ABC'。请用 ILIKE，或对两侧都应用 LOWER()；不过，LOWER(col) = 'abc' 无法利用 col 上的普通索引（如果经常需要这样查询，可在 LOWER(col) 上建立表达式索引）。
• 小心使用 IN ('a', 'b', NULL)：列表中的 NULL 没有带来任何好处，却会悄悄让 NOT IN 的行为出乎意料。

下载

SQL 文件： where.sql · 全部示例文件 · 准备工作