# WHERE

> 用比较、模式匹配、范围和 SQL 的三值逻辑过滤行。

---

LLMS 索引： [llms.txt](/llms.txt)

---

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

## 本章要点 {#learn}

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

## 示例 {#example}

```sql {num="4.1" caption="WHERE"}
-- 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` 连接的条件。

## 运行示例 {#run}

运行方式：

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

## 常见陷阱 {#pitfalls}

- `= 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` 的行为出乎意料。

> [!TIP] 提示
> 按前缀过滤文本时，`WHERE name LIKE 'foo%'` 可以使用具有合适排序规则或 `text_pattern_ops` 操作符类的 B-树索引。`WHERE name LIKE '%foo'`（后缀）或 `'%foo%'`（子串）则不行；这些情况可以考虑三字符组索引（`pg_trgm` 扩展）。

> [!NOTE] 动手练习
> 将条件改为 `WHERE schemaname = 'public'`；如果 `public` 中还没有表，就会得到零行。然后尝试 `WHERE tablename ~ '^pg_a.*'`，看看正则表达式如何匹配。

## 下载 {#downloads}

SQL 文件： [where.sql](/examples/source/where.sql) · [全部示例文件](/examples/examples.zip) · [准备工作](/#prerequisites)