PostgreSQL WHERE 子句

作者likuolei

PostgreSQL WHERE 子句完全指南

WHERE 是 SQL 的“守门员”，决定哪些行参与查询。
本文涵盖 基础语法、复杂条件、性能优化、索引利用、常见陷阱、NULL 处理、表达式、子查询、JSON/数组过滤 等全部内容。

一、WHERE 基本语法

SELECT column1, column2, ...
FROM table_name
WHERE condition;

核心原则：WHERE 条件返回 TRUE 的行才会被选中。

二、准备测试数据

CREATE TABLE employees (
    id BIGSERIAL PRIMARY KEY,
    name TEXT,
    email TEXT,
    age INT,
    department TEXT,
    salary NUMERIC(10,2),
    is_active BOOLEAN DEFAULT true,
    tags TEXT[],
    config JSONB,
    hire_date DATE,
    last_login TIMESTAMPTZ
);

-- 插入测试数据
INSERT INTO employees (name, email, age, department, salary, tags, config, hire_date, last_login)
VALUES 
('Alice', 'alice@company.com', 28, 'IT', 75000, '{"dev","lead"}', '{"level": 5}', '2023-01-15', '2025-11-02 09:00:00+00'),
('Bob', 'bob@company.com', 35, 'HR', 52000, '{"hr"}', '{"level": 3}', '2022-06-10', '2025-11-01 14:30:00+00'),
('Carol', NULL, 30, 'IT', 85000, '{"dev","ai"}', '{"level": 6}', '2024-03-20', NULL),
('Dave', 'dave@company.com', NULL, 'IT', 92000, '{"dev","lead"}', '{"level": 7}', '2021-11-05', '2025-11-03 08:15:00+00');

三、基础 WHERE 条件

类型示例说明
等于 / 不等于WHERE department = 'IT'字符串用单引号
比较WHERE salary > 70000> < >= <= <> !=
范围WHERE age BETWEEN 25 AND 35包含边界
列表WHERE name IN ('Alice', 'Bob')等价于多个 OR
模式匹配WHERE email LIKE '%@company.com'% 通配任意字符
忽略大小写WHERE email ILIKE '%alice%'ILIKE = 不区分大小写
正则WHERE name ~ '^A'~ 区分大小写，~* 不区分
NULLWHERE email IS NULL不能用 = NULL
-- 组合条件
SELECT name, salary 
FROM employees 
WHERE department = 'IT' 
  AND salary > 70000 
  AND is_active = true;

四、逻辑运算符组合

-- AND / OR / NOT
SELECT * FROM employees 
WHERE (department = 'IT' OR department = 'HR')
  AND salary BETWEEN 50000 AND 90000
  AND NOT name = 'Bob';

优先级：NOT > AND > OR
建议：用 () 明确逻辑，避免歧义！

五、NULL 处理（关键！）

错误写法正确写法
WHERE email = NULLWHERE email IS NULL
WHERE email != NULLWHERE email IS NOT NULL
-- 查找没有邮箱的员工
SELECT name FROM employees WHERE email IS NULL;

-- 查找有邮箱且最近登录的
SELECT name, last_login 
FROM employees 
WHERE email IS NOT NULL 
  AND last_login > NOW() - INTERVAL '7 days';

六、数组与 JSON 过滤

1. 数组（@>, <@, &&, = ANY）

-- 包含 "dev" 标签
SELECT name, tags FROM employees WHERE tags @> '{"dev"}';

-- 包含 "dev" 和 "lead"
SELECT name FROM employees WHERE tags @> '{"dev","lead"}';

-- 任意标签匹配
SELECT name FROM employees WHERE 'ai' = ANY(tags);

-- 标签有交集
SELECT name FROM employees WHERE tags && '{"hr","admin"}';

2. JSONB（->>, @>, ?）

-- 提取 level 并比较
SELECT name, config ->> 'level' AS level 
FROM employees 
WHERE (config ->> 'level')::INT >= 5;

-- JSON 包含特定结构
SELECT name FROM employees WHERE config @> '{"level": 6}';

-- 键存在
SELECT name FROM employees WHERE config ? 'level';

性能提示：为数组/JSON 列建 GIN 索引！

CREATE INDEX idx_emp_tags ON employees USING GIN (tags);
CREATE INDEX idx_emp_config ON employees USING GIN (config);

七、日期时间过滤

-- 今年入职
SELECT name, hire_date FROM employees 
WHERE hire_date >= '2025-01-01'::date;

-- 最近 30 天登录
SELECT name, last_login FROM employees 
WHERE last_login > NOW() - INTERVAL '30 days';

-- 按月分组（WHERE 中不能直接用聚合）
-- 错误：WHERE MONTH(hire_date) = 1
-- 正确：用表达式
SELECT name FROM employees 
WHERE EXTRACT(MONTH FROM hire_date) = 1;

-- 或用范围
SELECT name FROM employees 
WHERE hire_date BETWEEN '2025-01-01' AND '2025-01-31';

八、子查询在 WHERE 中

1. 标量子查询（返回单值）

-- 薪资高于 IT 部门平均
SELECT name, salary FROM employees 
WHERE salary > (
    SELECT AVG(salary) FROM employees WHERE department = 'IT'
);

2. 相关子查询（EXISTS 推荐）

-- 有登录记录的员工
SELECT name FROM employees e
WHERE EXISTS (
    SELECT 1 FROM login_logs l 
    WHERE l.user_id = e.id 
      AND l.login_time > NOW() - INTERVAL '1 month'
);

EXISTS 比 IN 更高效，尤其大数据量！

九、表达式在 WHERE 中

-- 字符串函数
SELECT name FROM employees 
WHERE LENGTH(name) > 5;

-- 算术
SELECT name, salary FROM employees 
WHERE salary * 1.1 > 100000;

-- 条件表达式
SELECT name FROM employees 
WHERE CASE 
    WHEN department = 'IT' THEN salary > 70000
    ELSE salary > 50000
END;

十、性能优化与索引利用

场景正确写法（可使用索引）错误写法（无法使用索引）
列比较WHERE department = 'IT'WHERE UPPER(department) = 'IT'
前缀匹配WHERE email LIKE 'alice%'WHERE email LIKE '%alice%'
范围WHERE hire_date >= '2025-01-01'WHERE TO_CHAR(hire_date, 'YYYY') = '2025'
JSONWHERE config ->> 'level' = '5' + 表达式索引WHERE config @> '{"level": 5}' 无索引

创建表达式索引

-- 加速忽略大小写搜索
CREATE INDEX idx_emp_name_lower ON employees(LOWER(name));

-- 加速 JSON 字段
CREATE INDEX idx_emp_level ON employees((config ->> 'level'));

-- 加速日期函数
CREATE INDEX idx_emp_hire_month ON employees(EXTRACT(MONTH FROM hire_date));

十一、常见陷阱与解决方案

陷阱说明解决方案
WHERE col = NULL永远不匹配用 IS NULL
WHERE func(col) = value无法用索引建表达式索引或改写
WHERE col IN (NULL, 1, 2)NULL 污染结果避免 IN (NULL, ...)
OR 条件复杂索引失效拆分为 UNION
隐式转换WHERE id = '123'显式转换或正确类型

十二、最佳实践 Checklist

项目建议
NULL 安全永远用 IS NULL / IS NOT NULL
索引友好避免在列上使用函数
逻辑清晰复杂条件用 () 分组
类型一致避免字符串与数字混用
数组/JSON用 @> + GIN 索引
EXISTS > IN相关子查询用 EXISTS
避免全表扫描常用过滤列建索引

十三、一键复杂 WHERE 查询示例

-- 查找：IT 部门、薪资 > 70k、包含 dev 标签、level >= 5、最近 7 天登录
SELECT name, salary, last_login
FROM employees
WHERE department = 'IT'
  AND salary > 70000
  AND tags @> '{"dev"}'
  AND (config ->> 'level')::INT >= 5
  AND last_login > NOW() - INTERVAL '7 days'
  AND is_active = true;

十四、快速上手：5 分钟掌握 WHERE

-- 1. 基础过滤
SELECT name, department FROM employees WHERE department = 'IT';

-- 2. 多条件 + NULL
SELECT name, email FROM employees 
WHERE email IS NOT NULL AND age BETWEEN 25 AND 35;

-- 3. 数组 + JSON
SELECT name, tags FROM employees 
WHERE tags @> '{"lead"}' AND config @> '{"level": 6}';

-- 4. 子查询
SELECT name, salary FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 5. 性能优化（建索引后）
CREATE INDEX idx_emp_dept_salary ON employees(department, salary);

现在就动手：
在 employees 表中执行以下查询：

  1. 查找 email 包含 company.com 且 age < 30 的员工
  2. 查找 tags 包含 dev 或 ai 且 salary > 80000 的员工
  3. 查找 last_login 在过去 3 天内的活跃用户（is_active = true）
  4. 查找 config.level >= 5 且 department = 'IT' 的员工（建表达式索引）

需要我生成：

  • 1000 条测试数据（含 NULL/JSON/数组）？回复 测试数据
  • 慢查询分析 + 优化建议？回复 慢查询优化
  • Python 中动态构建 WHERE 条件？回复 Python WHERE
  • WHERE vs HAVING 对比？回复 WHERE vs HAVING

随时告诉我！

类似文章

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注