多表查询：JOIN 与子查询

4.1 为什么需要 JOIN

关系型数据库的核心设计原则是数据规范化：把数据拆分到不同的表中，每张表只关注一件事，通过外键建立关联。查询时再通过 JOIN 把关联表的数据"拼接"起来。

本章使用的示例数据

4.2 CROSS JOIN — 笛卡尔积

笛卡尔积是两张表所有行的全组合。如果 A 有 3 行、B 有 4 行，CROSS JOIN 结果有 12 行。

SQL-- 笛卡尔积：用于生成组合数据（如排列组合测试）
SELECT u.username, p.title
FROM users u CROSS JOIN posts p;
-- 结果：3 × 3 = 9 行（每个用户与每篇文章配对）

-- 意外的笛卡尔积（漏写 JOIN 条件！）
SELECT * FROM users, posts;  -- 同上，应该避免

4.3 INNER JOIN — 内连接

INNER JOIN 只返回两张表中都有匹配记录的行。没有对应关系的行被丢弃。

SQL-- 查询文章及其作者姓名（INNER JOIN = JOIN，关键字等价）
SELECT
  p.id,
  p.title,
  u.username AS author,
  p.status
FROM posts p
INNER JOIN users u ON p.user_id = u.id
WHERE p.status = 'published'
ORDER BY p.id;

id	title	author	status
1	SQL 入门	alice	published
3	PostgreSQL 技巧	bob	published

carol 没有文章，所以不出现在结果中；draft 文章被 WHERE 过滤。

4.4 LEFT JOIN — 左连接

LEFT JOIN 保留左表（FROM 后面的表）的全部行，右表无匹配时对应列补 NULL。

SQL-- 查询所有用户及其文章数量（包括没有文章的用户）
SELECT
  u.username,
  COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username
ORDER BY post_count DESC;

username	post_count
alice	2
bob	1
carol	0

用 LEFT JOIN 查找"没有关联数据"的行

SQL-- 找出所有从未发文章的用户
SELECT u.username
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE p.id IS NULL;  -- 右表 id 为 NULL，说明没有匹配行

4.5 RIGHT JOIN — 右连接

RIGHT JOIN 是 LEFT JOIN 的镜像：保留右表全部行，左表无匹配补 NULL。实际开发中很少用 RIGHT JOIN，通常调换表的位置改用 LEFT JOIN 更易读。

SQL-- 这两个查询等价：
SELECT u.username, p.title
FROM users u RIGHT JOIN posts p ON u.id = p.user_id;

SELECT u.username, p.title
FROM posts p LEFT JOIN users u ON p.user_id = u.id;
-- 建议统一使用 LEFT JOIN，提高可读性

4.6 FULL OUTER JOIN — 全外连接

FULL OUTER JOIN 返回两张表的所有行，无论是否有匹配，无匹配的一侧补 NULL。MySQL 不支持 FULL OUTER JOIN，需用 UNION 模拟。

SQL-- PostgreSQL 原生支持
SELECT u.username, p.title
FROM users u
FULL OUTER JOIN posts p ON u.id = p.user_id;

-- MySQL 用 UNION 模拟 FULL OUTER JOIN
SELECT u.username, p.title
FROM users u LEFT JOIN posts p ON u.id = p.user_id
UNION
SELECT u.username, p.title
FROM users u RIGHT JOIN posts p ON u.id = p.user_id
WHERE u.id IS NULL;  -- 只取右表独有的部分，避免重复

4.7 自连接（Self JOIN）

一张表与自身 JOIN，常用于表示层级结构（员工-上级关系、分类-父分类）。

SQL-- 员工表（包含上级 ID）
CREATE TABLE employees (
  id        INT PRIMARY KEY,
  name      VARCHAR(50),
  manager_id INT NULL  -- CEO 的 manager_id 为 NULL
);

-- 查询每个员工及其直接上级姓名
SELECT
  e.name        AS employee,
  m.name        AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- 关键：同一张表用不同别名 e（员工）和 m（上级）

4.8 多表 JOIN

SQL-- 三表连接：查询文章、作者、评论数
SELECT
  p.id,
  p.title,
  u.username         AS author,
  COUNT(c.id)        AS comment_count
FROM posts p
INNER JOIN users    u ON p.user_id = u.id
LEFT  JOIN comments c ON p.id = c.post_id
WHERE p.status = 'published'
GROUP BY p.id, p.title, u.username
ORDER BY comment_count DESC;

4.9 子查询（Subquery）

子查询是嵌套在另一个查询内部的查询，用括号括起来。根据位置分为三种：

WHERE 子查询

SQL-- 查询发过文章的用户（使用子查询）
SELECT username, email
FROM users
WHERE id IN (
  SELECT DISTINCT user_id FROM posts
);

-- 查询价格高于平均价格的商品
SELECT name, price
FROM products
WHERE price > (
  SELECT AVG(price) FROM products
);

FROM 子查询（派生表）

SQL-- FROM 后面的子查询叫做"派生表"，必须给别名
SELECT author, COUNT(*) AS published_count
FROM (
  SELECT u.username AS author
  FROM posts p
  INNER JOIN users u ON p.user_id = u.id
  WHERE p.status = 'published'
) AS published_posts   -- 必须有别名
GROUP BY author;

SELECT 子查询（标量子查询）

SQL-- 在 SELECT 列表中嵌入子查询（每行都执行一次，性能注意）
SELECT
  u.username,
  (SELECT COUNT(*) FROM posts p WHERE p.user_id = u.id) AS post_count
FROM users u;
-- 标量子查询必须只返回一行一列，否则报错

相关子查询（Correlated Subquery）

SQL-- 相关子查询：子查询引用了外层查询的列（每行都重新计算）
-- 找出每个用户最新一篇文章
SELECT u.username, p.title, p.created_at
FROM posts p
INNER JOIN users u ON p.user_id = u.id
WHERE p.created_at = (
  SELECT MAX(p2.created_at)
  FROM posts p2
  WHERE p2.user_id = p.user_id  -- 引用外层的 p.user_id
);

4.10 EXISTS 和 NOT EXISTS

EXISTS 检查子查询是否返回至少一行，返回布尔值。与 IN 相比，当子查询结果集很大时，EXISTS 通常更高效（找到第一个匹配就停止）。

SQL-- EXISTS：查询发过文章的用户
SELECT username
FROM users u
WHERE EXISTS (
  SELECT 1  -- EXISTS 只关心是否有行，SELECT 什么无所谓，写 1 最清晰
  FROM posts p
  WHERE p.user_id = u.id
);

-- NOT EXISTS：查询从未发文章的用户
SELECT username
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM posts p WHERE p.user_id = u.id
);

4.11 UNION 和 UNION ALL

SQL-- UNION：合并两个查询结果集，自动去重
SELECT username AS name, email FROM users
UNION
SELECT contact_name, contact_email FROM customers;
-- 两个子查询的列数和类型必须一致
-- UNION 会对结果去重（等于执行了 DISTINCT），代价是额外排序

-- UNION ALL：合并不去重（保留重复行），性能更好
SELECT id, title, 'post' AS type FROM posts
UNION ALL
SELECT id, content, 'comment' AS type FROM comments;
-- 合并两种不同类型的内容到一个结果集，type 列区分来源

-- 对 UNION 结果排序（ORDER BY 放在最后）
SELECT username, created_at FROM users
UNION ALL
SELECT product_name, created_at FROM orders
ORDER BY created_at DESC
LIMIT 10;