PostgreSQL SELECT 与 JOIN

SELECT 是 SQL 里最常用、最有学问的语句——会写 SELECT 是数据库入门的标志,会写高效的 SELECT 是工程师的核心竞争力。本篇系统讲清 SELECT 的所有常用子句、四种 JOIN,以及必备的 EXPLAIN 调优工具。

1. 基础查询三件套:WHERE / ORDER BY / LIMIT

日常 80% 的查询是"过滤 → 排序 → 分页"。先看最基础的写法:

-- 基础查询:投影、过滤、排序、分页
SELECT name, email, age FROM users;

-- 过滤 + 排序 + 分页
SELECT name, email, age
FROM users
WHERE age >= 18
ORDER BY age DESC, name ASC     -- 多列排序,DESC 降序 ASC 升序
LIMIT 10 OFFSET 0;              -- 第一页,每页 10 行

-- 等价写法:OFFSET-FETCH(SQL 标准)
SELECT name, age FROM users
ORDER BY id
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

-- DISTINCT 去重
SELECT DISTINCT city FROM users;
-- 多列组合去重
SELECT DISTINCT city, country FROM users;

LIMIT + OFFSET 是分页的标准做法,但大表深翻页会变慢(OFFSET 100000 要先扫前 10 万行)。生产环境深分页推荐用"游标分页"(WHERE id > last_seen_id ORDER BY id LIMIT 10)。

2. WHERE — 行级过滤

WHERE 是 SQL 里条件最多的子句,运算符极其丰富:

-- WHERE 的各种条件运算符
SELECT * FROM users WHERE age = 25;            -- 等于
SELECT * FROM users WHERE age <> 25;           -- 不等于(标准)
SELECT * FROM users WHERE age != 25;           -- 不等于(非标准但支持)
SELECT * FROM users WHERE age > 18 AND age < 60;
SELECT * FROM users WHERE age < 18 OR age > 60;
SELECT * FROM users WHERE NOT deleted;         -- 布尔取反

-- BETWEEN 范围(包含两端)
SELECT * FROM users WHERE age BETWEEN 18 AND 60;

-- IN 集合
SELECT * FROM users WHERE role IN ('admin', 'editor', 'vip');

-- IS NULL / IS NOT NULL(NULL 判断必须用 IS,不能用 = )
SELECT * FROM users WHERE deleted_at IS NULL;

-- LIKE 模式匹配(% 任意多字符,_ 单个字符)
SELECT * FROM users WHERE name LIKE '小%';       -- 以"小"开头
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- ILIKE 不区分大小写(Postgres 特色)
SELECT * FROM users WHERE name ILIKE 'john%';

-- 正则 ~ / ~*(不区分大小写)/ !~ 不匹配
SELECT * FROM users WHERE name ~ '^小[明清]';

-- ANY / ALL 配合数组
SELECT * FROM users WHERE id = ANY(ARRAY[1, 2, 3]);
SELECT * FROM users WHERE age > ALL(ARRAY[10, 20, 30]);

-- 多条件组合(注意优先级)
SELECT * FROM users
WHERE (role = 'admin' OR role = 'editor')
  AND age >= 18
  AND deleted_at IS NULL;

几个易错点:

NULL 判断必须用 IS NULL:WHERE deleted_at = NULL 永远返回空(因为 NULL = NULL 也是 NULL,不是 true)。
LIKE 的 % 通配符会阻止索引：LIKE 'abc%'(前缀匹配)能走索引，LIKE '%abc' 走不了。
ILIKE 是 Postgres 特色：MySQL 没有不区分大小写的 LIKE。

3. 聚合与 GROUP BY

聚合函数（COUNT、SUM、AVG、MIN、MAX）把多行压成一个值。配合 GROUP BY 分组，就能统计"每个分类的指标"——这是 SQL 做报表的核心能力。

-- 聚合函数:把多行压成一个值
SELECT
    COUNT(*)          AS total,         -- 总行数(含 NULL)
    COUNT(email)      AS with_email,    -- email 非 NULL 的行数
    COUNT(DISTINCT role) AS roles,      -- 不同 role 的数量
    AVG(age)          AS avg_age,       -- 平均
    SUM(salary)       AS total_salary,  -- 求和
    MIN(age)          AS min_age,       -- 最小
    MAX(age)          AS max_age        -- 最大
FROM users;

-- GROUP BY 分组(按某列的值相同的行合并)
-- 配合聚合函数,统计每个分组的指标
SELECT
    role,
    COUNT(*)        AS user_count,
    AVG(age)        AS avg_age,
    MAX(created_at) AS latest
FROM users
WHERE deleted_at IS NULL
GROUP BY role
ORDER BY user_count DESC;

-- HAVING:分组后过滤(对应 WHERE 的"行级过滤",HAVING 是"组级过滤")
SELECT
    role,
    COUNT(*) AS cnt
FROM users
GROUP BY role
HAVING COUNT(*) > 10
ORDER BY cnt DESC;

-- 多列分组
SELECT role, country, COUNT(*)
FROM users
GROUP BY role, country
ORDER BY role, country;

三个高频考点：

COUNT(*) vs COUNT(col)：前者统计所有行（含 NULL），后者只统计 col 非 NULL 的行。性能上 Postgres 优化得几乎一样。
WHERE vs HAVING：WHERE 在分组前过滤（行级），HAVING 在分组后过滤（组级，能用聚合函数）。
DISTINCT 在聚合里：COUNT(DISTINCT col) 统计不同值的数量，去重统计必备。

4. JOIN — 把多表拼起来

真实业务数据分散在多张表（用户表、订单表、商品表），JOIN 的作用就是按某字段把它们关联起来，拼成一张结果表。四种 JOIN 一句话区分：

-- 先准备两张表
CREATE TABLE users (
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT UNIQUE
);
CREATE TABLE orders (
    id         SERIAL PRIMARY KEY,
    user_id    INT REFERENCES users(id),
    total      NUMERIC(10,2),
    created_at TIMESTAMPTZ DEFAULT now()
);

-- === INNER JOIN ===
-- 只返回两边都匹配的行(用户没有订单的不出现)
SELECT u.name, o.total, o.created_at
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total > 100;

-- === LEFT JOIN ===
-- 左边全保留,右边没匹配则填 NULL(适合"列出所有用户及其订单")
SELECT u.name, COALESCE(o.total, 0) AS spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- COALESCE 把 NULL 替换成 0,避免报表出现 NULL

-- === RIGHT JOIN ===
-- 右边全保留(订单的所有行都出现,即便用户被删了)
SELECT u.name, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- === FULL OUTER JOIN ===
-- 两边全保留,没匹配的部分都填 NULL
SELECT u.name, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- === 多表 JOIN ===
SELECT u.name, o.total, p.title
FROM users u
JOIN orders o     ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p   ON oi.product_id = p.id
WHERE u.role = 'vip';

-- === 自连接(self join):同一张表连自己 ===
-- 找出"员工-经理"关系(同一张 employees 表)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

四种 JOIN 记忆口诀：

INNER JOIN：取两边都有的（交集）。
LEFT JOIN：左边全保留，右边缺失补 NULL。
RIGHT JOIN：右边全保留（很少用，通常改写为 LEFT）。
FULL OUTER JOIN：两边全保留，都没的部分补 NULL。

日常 80% 场景用 INNER 和 LEFT 就够。LEFT JOIN 是默认推荐——它语义清晰（“列出所有 X，以及匹配的 Y”），不会因为 Y 缺失而丢数据。

COALESCE(x, 0) 是把 NULL 替换成默认值的小函数，做报表时极有用（避免 NULL 出现在财务统计里）。

5. 子查询 vs JOIN

同一个问题往往能用子查询或 JOIN 两种写法解决。比如“找出下过单的用户”：

-- 写法 A:子查询 + IN
SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders);

-- 写法 B:JOIN + DISTINCT
SELECT DISTINCT u.name
FROM users u
JOIN orders o ON u.id = o.user_id;

-- 写法 C:EXISTS(性能通常最好,尤其子表大时)
SELECT name FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 三种写法结果一样,性能视数据量而定
-- Postgres 优化器很聪明,通常会把 IN 自动改写为 JOIN

EXISTS 的优势是“找到一行匹配就停止”，子表大时不需扫描全部。复杂查询优先用 EXISTS。

6. EXPLAIN — 看数据库“怎么想”

SELECT 写出来后，怎么知道它快不快？答案是用 EXPLAIN 看执行计划——数据库告诉你它打算怎么执行这条 SQL。

-- EXPLAIN:看执行计划(数据库打算怎么执行这条 SQL)
EXPLAIN SELECT * FROM users WHERE email = 'xm@example.com';

-- EXPLAIN ANALYZE:真的执行一遍,显示实际耗时
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'xm@example.com';

-- 输出示例(走索引):
-- Index Scan using users_email_key on users  (cost=0.29..8.30 rows=1 ...)
--   Index Cond: (email = 'xm@example.com'::text)
--   Execution Time: 0.123 ms

-- 输出示例(没走索引,全表扫描 = 慢!):
-- Seq Scan on users  (cost=0.00..15.50 rows=1 ...)
--   Filter: (email = 'xm@example.com'::text)
--   Execution Time: 1.234 ms

-- 优化慢查询的第一步永远是 EXPLAIN ANALYZE:
--   1. 看"Seq Scan"(全表扫描)→ 该建索引了
--   2. 看"rows"预估是否准确 → 不准说明统计信息过期,跑 ANALYZE 表名
--   3. 看"Execution Time" → 真实耗时

读 EXPLAIN 输出的三个关键词：

Seq Scan（顺序扫描）：全表扫，通常慢，需要加索引。
Index Scan（索引扫描）：走索引，快。
Bitmap Heap Scan：先索引定位再批量取，介于两者之间。

慢查询优化第一步永远是 EXPLAIN ANALYZE。看不懂执行计划，就不算真正懂 SQL。

小结

这一章你掌握了 SELECT 的全套基础：WHERE 过滤、GROUP BY 分组、JOIN 多表关联、EXPLAIN 看执行计划。下一篇我们看 PostgreSQL 的内置函数与运算符——在 SELECT 里你能做的远不止“取列”。

← 上一篇 PostgreSQL DML

下一篇 PostgreSQL 函数与运算符 →

✈️💬