跳到主要内容

表表达式

表表达式描述查询要从哪里取得行，以及这些行如何被连接、筛选和分组。它通常由 FROM 开始，并按需接上 WHERE、GROUP BY 和 HAVING：

SELECT c.customer_id, c.name, SUM(o.total_amount) AS paid_amount
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.status = 'paid'
GROUP BY c.customer_id, c.name
HAVING SUM(o.total_amount) >= 1000;

可以把这段查询理解为一条加工流水线：先建立数据来源，再筛选行，然后形成分组，最后筛选分组。表表达式产生的是一个临时结果表，外层的 SELECT 再从中选择输出列。

本文以通用 SQL 为主。LATERAL、表函数和高级分组的支持程度因数据库及版本而异。

表表达式的处理阶段​

表表达式常见的逻辑处理顺序如下：

  1. FROM：读取表、子查询等数据源，并完成连接；
  2. WHERE：删除条件不为 TRUE 的行；
  3. GROUP BY：把剩余行划分成组；
  4. HAVING：删除条件不为 TRUE 的组。

这个顺序解释了为什么 WHERE 不能使用聚合结果：执行 WHERE 时，分组和聚合在逻辑上尚未发生。聚合后的条件应写在 HAVING 中。

FROM：声明数据来源​

最简单的数据来源是一张表：

SELECT customer_id, name
FROM customers;

FROM 后面还可以放置视图、子查询、公共表表达式、VALUES 结果以及数据库支持的表函数。它们都必须产生“零行或多行、每行包含若干列”的表状结果。

表别名​

别名用于缩短名称并区分不同数据源：

SELECT c.customer_id, c.name
FROM customers AS c;

定义别名后，同一查询层通常必须使用别名引用该表，不能再混用原表名。AS 在表别名中通常可以省略，但保留它更容易辨认：

FROM customers c

同一张表参与多次查询时，别名尤其重要。例如查询员工及其直属主管：

SELECT e.name AS employee_name, m.name AS manager_name
FROM employees AS e
LEFT JOIN employees AS m ON m.employee_id = e.manager_id;

这称为自连接。e 和 m 是同一张物理表在查询中的两个独立角色。

多个独立数据源​

逗号分隔的数据源会形成笛卡尔积：

SELECT s.size_name, c.color_name
FROM sizes AS s, colors AS c;

如果 sizes 有 3 行、colors 有 4 行，结果就有 12 行。更推荐使用意图明确的 CROSS JOIN：

SELECT s.size_name, c.color_name
FROM sizes AS s
CROSS JOIN colors AS c;

除非业务确实需要所有组合，否则应检查是否漏写了连接条件。

派生表：把子查询作为数据源​

放在 FROM 中的子查询称为派生表：

SELECT t.customer_id, t.paid_amount
FROM (
  SELECT customer_id, SUM(total_amount) AS paid_amount
  FROM orders
  WHERE status = 'paid'
  GROUP BY customer_id
) AS t
WHERE t.paid_amount >= 1000;

派生表必须用圆括号包围，并且大多数数据库要求为它设置别名。外层查询只能看到子查询输出的列，看不到子查询没有选择的原始列。

也可以为派生表及其列统一命名：

FROM (
  SELECT customer_id, SUM(total_amount)
  FROM orders
  GROUP BY customer_id
) AS totals(customer_id, paid_amount)

复杂逻辑需要多处引用时，使用 WITH 查询通常比重复派生表更清楚。

连接表​

连接把两个表表达式横向组合。基本形式是：

left_table
JOIN right_table ON join_condition

INNER JOIN​

内连接只保留连接条件为 TRUE 的组合：

SELECT c.name, o.order_id, o.total_amount
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.customer_id;

一个客户有多张订单时，该客户会出现在多行中。这是“一对多”关系展开后的正常结果，不应随意用 DISTINCT 掩盖。

LEFT JOIN​

左连接保留左侧全部行。右侧没有匹配时，右侧各列以 NULL 补齐：

SELECT c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id;

要保留所有客户、但只匹配已支付订单，应把右表条件写进 ON：

SELECT c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'paid';

如果把 o.status = 'paid' 放到 WHERE，没有匹配订单的行会因 o.status 为 NULL 而被过滤，左连接就会表现得接近内连接。

RIGHT JOIN 与 FULL OUTER JOIN​

RIGHT JOIN 保留右侧全部行，通常可以交换左右表后改写为更常见的 LEFT JOIN。FULL OUTER JOIN 保留两侧全部行：能匹配的合并，不能匹配的一侧以 NULL 补齐。

SELECT a.account_id, b.account_id
FROM old_accounts AS a
FULL OUTER JOIN new_accounts AS b
  ON b.account_id = a.account_id;

并非所有数据库都原生支持 FULL OUTER JOIN 或 RIGHT JOIN，使用前应核对目标产品。

ON、USING 与 NATURAL​

ON 可以写任意连接条件，也是最清晰、最通用的形式：

FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id

当两侧连接列同名时，可以使用 USING：

FROM orders
JOIN customers USING (customer_id)

USING (customer_id) 等价于按同名列相等进行连接，并且结果通常只保留一个 customer_id 输出列。多个列可以写成 USING (store_id, product_id)。

NATURAL JOIN 会自动使用两侧所有同名列：

FROM orders NATURAL JOIN customers

生产代码通常不建议这样写。任何一张表新增同名列，都可能悄悄改变连接条件和结果。显式写出 ON 或 USING 更容易审查。

连接的结合与括号​

多个 JOIN 可以连续书写：

FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
JOIN order_items AS i ON i.order_id = o.order_id

内连接在条件等价时通常可以由优化器重排，但外连接的顺序会影响“哪一侧必须保留”。需要改变默认结合方式时，应使用括号明确分组：

FROM stores AS s
LEFT JOIN (
  orders AS o
  JOIN order_items AS i ON i.order_id = o.order_id
) ON o.store_id = s.store_id

不要仅凭排版猜测连接范围。阅读复杂连接时，逐个确认每次连接的左侧结果、右侧数据源和保留规则。

相关数据源与 LATERAL​

普通派生表通常不能引用同级 FROM 中位于它前面的数据源。支持 LATERAL 的数据库允许右侧子查询引用左侧当前行：

SELECT c.customer_id, recent.order_id, recent.created_at
FROM customers AS c
LEFT JOIN LATERAL (
  SELECT o.order_id, o.created_at
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
  ORDER BY o.created_at DESC, o.order_id DESC
  FETCH FIRST 1 ROW ONLY
) AS recent ON TRUE;

它表达“针对每位客户取最近一张订单”。LATERAL 的名称、支持程度以及与 APPLY 的对应关系存在方言差异；SQL Server 常用 CROSS APPLY 或 OUTER APPLY 表达类似需求。

WHERE：筛选行​

WHERE 对 FROM 产生的每一行计算条件，只保留结果为 TRUE 的行：

SELECT order_id, total_amount
FROM orders
WHERE status = 'paid'
  AND total_amount >= 100;

条件为 FALSE 或 UNKNOWN 的行都会被删除。由于涉及 NULL 的比较经常得到 UNKNOWN，空值判断必须使用 IS NULL 或 IS NOT NULL：

WHERE cancelled_at IS NULL

WHERE 可以引用当前查询层中 FROM 提供的列，但通常不能引用同层 SELECT 才定义的别名。

GROUP BY：形成分组​

GROUP BY 把分组键相同的行放入同一组，聚合函数再对每组计算：

SELECT customer_id, COUNT(*) AS order_count, SUM(total_amount) AS amount
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;

在标准 SQL 的基本规则下，SELECT 中每个未被聚合的表达式都应由分组键确定。最稳妥的写法是把非聚合列明确列在 GROUP BY 中：

SELECT customer_id, status, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, status;

没有 GROUP BY 但使用了聚合函数时，所有输入行被视为一个组。因此下面的查询始终最多返回一行：

SELECT COUNT(*) AS order_count
FROM orders
WHERE status = 'paid';

高级分组​

支持标准高级分组语法的数据库可以在一条查询中生成多种汇总层级：

SELECT store_id, status, SUM(total_amount) AS amount
FROM orders
GROUP BY GROUPING SETS (
  (store_id, status),
  (store_id),
  ()
);

这里依次产生“门店与状态”“仅门店”和“全部数据”三个层级。ROLLUP 适合层级小计，CUBE 适合所有维度组合。不同数据库对这些语法和 GROUPING() 函数的支持不同，而且组合数量可能快速增长。

HAVING：筛选分组​

HAVING 在分组后执行，因此可以使用聚合结果：

SELECT customer_id, SUM(total_amount) AS paid_amount
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(total_amount) >= 1000;

能在分组前确定的普通行条件应优先放在 WHERE，这样语义更清楚，也能减少参与分组的数据量：

-- status 是行条件，放在 WHERE
WHERE status = 'paid'

-- SUM(...) 是组条件，放在 HAVING
HAVING SUM(total_amount) >= 1000

即使没有显式 GROUP BY，HAVING 也可能把整个输入视为一个组，但这种写法的可读性和方言兼容性需要谨慎评估。

完整阅读方法​

面对复杂表表达式，可以按下面的顺序检查：

  1. 列出每个 FROM 数据源及其别名；
  2. 标明每个连接的类型、条件和关系基数；
  3. 估算连接后可能产生多少行；
  4. 判断 WHERE 会删除哪些行，特别是外连接产生的 NULL 行；
  5. 找出分组键，并确认非聚合列是否合法；
  6. 判断 HAVING 删除哪些组；
  7. 最后再阅读 SELECT 输出和 ORDER BY。

表表达式的核心是逐步确定“哪些行可供选择”。把数据来源、行筛选和组筛选分开思考，复杂查询就更容易验证。