第五章：连接查询 - SQL 速成教程

1 连接查询概述

关系型数据库通过范式设计将数据分散到多张表中，避免冗余。但实际查询时，我们经常需要从多张表中组合数据——这就是 JOIN 的用武之地。JOIN 是 SQL 中最强大也最核心的特性之一。

示例数据

本章所有示例基于以下四张表：

CREATE TABLE users (
    id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(100) NOT NULL,
    email VARCHAR(200) NOT NULL UNIQUE
);

CREATE TABLE products (
    id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

CREATE TABLE orders (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED NOT NULL,
    order_date DATE NOT NULL,
    status     ENUM('pending', 'paid', 'shipped', 'completed') DEFAULT 'pending',
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE order_items (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id   INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    quantity   SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    unit_price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 插入示例数据
INSERT INTO users (name, email) VALUES
    ('张三', 'zhangsan@example.com'),
    ('李四', 'lisi@example.com'),
    ('王五', 'wangwu@example.com'),
    ('赵六', 'zhaoliu@example.com');

INSERT INTO products (name, price) VALUES
    ('笔记本电脑', 5999.00),
    ('无线鼠标', 89.00),
    ('机械键盘', 399.00),
    ('显示器', 1599.00);

INSERT INTO orders (user_id, order_date, status) VALUES
    (1, '2024-01-15', 'completed'),
    (1, '2024-03-20', 'paid'),
    (2, '2024-02-10', 'shipped'),
    (3, '2024-04-01', 'pending');

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
    (1, 1, 1, 5999.00),
    (1, 2, 2, 89.00),
    (2, 3, 1, 399.00),
    (3, 1, 1, 5999.00),
    (3, 4, 2, 1599.00);

注意：赵六（id=4）没有任何订单，商品"显示器"在订单 3 中被购买。

2 INNER JOIN

INNER JOIN（内连接）返回两张表中满足连接条件的交集部分。如果某一行在另一张表中没有匹配，则不出现在结果中。

基本语法

SELECT 列名
FROM 表A
INNER JOIN 表B ON 表A.列 = 表B.列;

查询用户及其订单

SELECT u.name AS 用户名, o.id AS 订单号, o.order_date AS 下单日期, o.status AS 状态
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 结果：
-- +--------+--------+------------+-----------+
-- | 用户名 | 订单号 | 下单日期   | 状态      |
-- +--------+--------+------------+-----------+
-- | 张三   |      1 | 2024-01-15 | completed |
-- | 张三   |      2 | 2024-03-20 | paid      |
-- | 李四   |      3 | 2024-02-10 | shipped   |
-- | 王五   |      4 | 2024-04-01 | pending   |
-- +--------+--------+------------+-----------+
-- 注意：赵六没有订单，所以不出现在结果中

多表 JOIN

-- 查询完整的订单详情：用户名 + 订单信息 + 商品名 + 数量 + 金额
SELECT
    u.name    AS 用户名,
    o.id      AS 订单号,
    p.name    AS 商品名,
    oi.quantity AS 数量,
    oi.unit_price AS 单价,
    (oi.quantity * oi.unit_price) AS 小计
FROM users u
INNER JOIN orders o      ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p    ON oi.product_id = p.id
ORDER BY o.id, p.name;

别名让查询更可读

-- 没有别名——冗长难读
SELECT users.name, orders.id, orders.order_date
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- 使用别名——简洁清晰
SELECT u.name, o.id, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

⚡ JOIN 与 WHERE 的关系

INNER JOIN ... ON 也可以写成 FROM A, B WHERE A.id = B.a_id（隐式连接），但推荐使用显式 JOIN 语法——更清晰地分离连接条件和过滤条件。

3 LEFT JOIN / RIGHT JOIN

LEFT JOIN（左连接）

LEFT JOIN 返回左表的所有行，即使右表中没有匹配。没有匹配时，右表的列填充 NULL。

-- 查询所有用户及其订单数（包括没有订单的用户）
SELECT u.name AS 用户名, COUNT(o.id) AS 订单数
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- 结果：
-- +--------+--------+
-- | 用户名 | 订单数 |
-- +--------+--------+
-- | 张三   |      2 |
-- | 李四   |      1 |
-- | 王五   |      1 |
-- | 赵六   |      0 |  ← LEFT JOIN 保留了赵六
-- +--------+--------+

查找不匹配的行

-- 查找从未下过订单的用户
SELECT u.name, u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- 结果：
-- +--------+---------------------+
-- | name   | email               |
-- +--------+---------------------+
-- | 赵六   | zhaoliu@example.com |
-- +--------+---------------------+

RIGHT JOIN（右连接）

RIGHT JOIN 与 LEFT JOIN 相反——保留右表的所有行。实际上 A RIGHT JOIN B 等价于 B LEFT JOIN A。大多数项目统一使用 LEFT JOIN，更易理解。

-- RIGHT JOIN 示例（等价于反转的 LEFT JOIN）
SELECT o.id AS 订单号, u.name AS 用户名
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;

-- 等价写法（推荐这种，更直观）
SELECT o.id AS 订单号, u.name AS 用户名
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

LEFT JOIN 的常见陷阱

-- ❌ 错误：WHERE 条件会过滤掉 NULL 行，LEFT JOIN 退化为 INNER JOIN
SELECT u.name, o.id, o.status
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';
-- 赵六被过滤掉了！因为他的 o.status 是 NULL

-- ✅ 正确：将过滤条件放在 ON 子句中
SELECT u.name, o.id, o.status
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed';
-- 赵六保留，o.id 和 o.status 为 NULL

⚡ ON vs WHERE 的区别

ON 子句：在生成连接结果时过滤——不影响左表行的保留
WHERE 子句：在连接结果生成后过滤——会过滤掉不满足条件的行（包括 NULL 行）
对于 INNER JOIN，ON 和 WHERE 效果相同；对于 LEFT/RIGHT JOIN，效果不同

4 CROSS JOIN

CROSS JOIN（交叉连接）产生两张表的笛卡尔积——左表的每一行与右表的每一行组合。如果左表有 M 行、右表有 N 行，结果集有 M × N 行。

-- 基本语法
SELECT * FROM 表A CROSS JOIN 表B;
-- 等价写法
SELECT * FROM 表A, 表B;

实际用途：生成日期范围

-- 生成连续日期（利用递归 CTE，MySQL 8.0+）
WITH RECURSIVE dates AS (
    SELECT '2024-01-01' AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM dates WHERE dt < '2024-01-07'
)
SELECT dt FROM dates;

-- CROSS JOIN 生成所有用户 × 日期的组合（用于填充报表空行）
WITH RECURSIVE dates AS (
    SELECT '2024-01-01' AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM dates WHERE dt < '2024-01-07'
)
SELECT u.name, d.dt
FROM users u
CROSS JOIN dates d
ORDER BY u.name, d.dt;

实际用途：生成组合

-- 生成所有颜色 × 尺码的 SKU 组合
CREATE TEMPORARY TABLE colors (color VARCHAR(20));
CREATE TEMPORARY TABLE sizes (size VARCHAR(10));

INSERT INTO colors VALUES ('红色'), ('蓝色'), ('黑色');
INSERT INTO sizes VALUES ('S'), ('M'), ('L'), ('XL');

SELECT
    CONCAT(c.color, '-', s.size) AS sku,
    c.color,
    s.size
FROM colors c
CROSS JOIN sizes s
ORDER BY c.color, s.size;

-- 结果：12 行（3 色 × 4 码）

5 自连接 (Self Join)

自连接是指一张表与自身进行 JOIN。通过为同一张表取不同的别名，可以将表中的行相互关联。最经典的场景是员工-经理层级关系。

员工-经理层级

CREATE TABLE employees (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    manager_id INT UNSIGNED,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

INSERT INTO employees (name, manager_id) VALUES
    ('王总',   NULL),
    ('张经理', 1),
    ('李经理', 1),
    ('赵主管', 2),
    ('钱工程师', 2),
    ('孙工程师', 3),
    ('周实习生', 4);

-- 查询每个员工及其直属上级
SELECT
    e.name  AS 员工,
    m.name  AS 上级
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- 结果：
-- +----------+--------+
-- | 员工     | 上级   |
-- +----------+--------+
-- | 王总     | NULL   |
-- | 张经理   | 王总   |
-- | 李经理   | 王总   |
-- | 赵主管   | 张经理 |
-- | 钱工程师 | 张经理 |
-- | 孙工程师 | 李经理 |
-- | 周实习生 | 赵主管 |
-- +----------+--------+

查找同一层级的同事对

-- 查找同一个经理下的同事对
SELECT
    e1.name AS 员工1,
    e2.name AS 员工2,
    m.name  AS 共同上级
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.manager_id AND e1.id < e2.id
INNER JOIN employees m  ON e1.manager_id = m.id;

-- 结果：
-- +-----------+-----------+----------+
-- | 员工1     | 员工2     | 共同上级 |
-- +-----------+-----------+----------+
-- | 张经理    | 李经理    | 王总     |
-- | 赵主管    | 钱工程师  | 张经理   |
-- +-----------+-----------+----------+
-- e1.id < e2.id 避免重复配对（张经理-李经理 和 李经理-张经理）

递归查询：获取完整层级链

-- MySQL 8.0+ 递归 CTE
WITH RECURSIVE hierarchy AS (
    SELECT id, name, manager_id, 1 AS level, CAST(name AS CHAR(500)) AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.manager_id, h.level + 1,
           CONCAT(h.path, ' → ', e.name)
    FROM employees e
    INNER JOIN hierarchy h ON e.manager_id = h.id
)
SELECT name AS 员工, level AS 层级, path AS 汇报链
FROM hierarchy
ORDER BY level, name;

-- 结果：
-- +----------+------+------------------------------+
-- | 员工     | 层级 | 汇报链                       |
-- +----------+------+------------------------------+
-- | 王总     |    1 | 王总                         |
-- | 李经理   |    2 | 王总 → 李经理                |
-- | 张经理   |    2 | 王总 → 张经理                |
-- | 孙工程师 |    3 | 王总 → 李经理 → 孙工程师     |
-- | 赵主管   |    3 | 王总 → 张经理 → 赵主管       |
-- | 钱工程师 |    3 | 王总 → 张经理 → 钱工程师     |
-- | 周实习生 |    4 | 王总 → 张经理 → 赵主管 → 周实习生 |
-- +----------+------+------------------------------+

6 子查询

子查询（Subquery）是嵌套在另一个 SQL 语句内部的 SELECT 查询。根据返回结果的形式，可分为标量子查询、列子查询、行子查询和表子查询。

标量子查询 — 返回单个值

-- 查找最贵的商品
SELECT name, price
FROM products
WHERE price = (SELECT MAX(price) FROM products);

-- 结果：笔记本电脑, 5999.00

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

-- 在 SELECT 中使用标量子查询
SELECT
    name,
    price,
    price - (SELECT AVG(price) FROM products) AS 偏差
FROM products;

IN 子查询 — 返回一列多值

-- 查找下过订单的用户
SELECT name, email
FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);

-- 查找被购买过的商品
SELECT name, price
FROM products
WHERE id IN (SELECT DISTINCT product_id FROM order_items);

-- NOT IN：查找从未被购买的商品
SELECT name, price
FROM products
WHERE id NOT IN (SELECT DISTINCT product_id FROM order_items);

EXISTS 子查询 — 存在性检查

-- 查找下过订单的用户（EXISTS 版本）
SELECT u.name, u.email
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 查找从未下过订单的用户（NOT EXISTS 版本）
SELECT u.name, u.email
FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 结果：赵六, zhaoliu@example.com

FROM 子查询（派生表）

-- 先计算每个用户的订单统计，再从中筛选
SELECT user_summary.用户名, user_summary.订单数, user_summary.总金额
FROM (
    SELECT
        u.name AS 用户名,
        COUNT(o.id) AS 订单数,
        COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS 总金额
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    LEFT JOIN order_items oi ON o.id = oi.order_id
    GROUP BY u.id, u.name
) AS user_summary
WHERE user_summary.总金额 > 1000
ORDER BY user_summary.总金额 DESC;

⚡ 子查询 vs JOIN 性能

EXISTS / IN 子查询：MySQL 优化器在很多情况下会自动改写为 JOIN（半连接优化）
相关子查询（引用外层表的列）：每一行都要执行一次子查询，大数据量时可能很慢
一般建议：能用 JOIN 写的优先用 JOIN，逻辑更清晰且优化器更容易处理
使用 EXPLAIN 查看实际执行计划，而非依赖经验判断

🔄 MySQL vs PostgreSQL 子查询优化

MySQL：早期版本（5.6 以前）子查询优化较弱，IN 子查询可能逐行扫描外表。MySQL 8.0 大幅改进，支持半连接、物化等优化策略。
PostgreSQL：子查询优化一直很成熟，支持子查询展开（subquery unnesting）、HashSetOp 等策略。相关子查询通常自动改写为 JOIN。
通用建议：两个数据库中，EXISTS 通常比 IN 更可靠——因为 IN 碰到 NULL 值时行为可能出乎意料。

7 UNION 与 UNION ALL

UNION 将多个 SELECT 语句的结果集纵向合并。与 JOIN 的横向拼接不同，UNION 是上下堆叠。

UNION — 去重合并

-- 合并两个查询结果，自动去除重复行
SELECT name, '用户' AS 来源 FROM users
UNION
SELECT name, '商品' AS 来源 FROM products;

-- UNION 会对结果集进行排序去重，有一定性能开销

UNION ALL — 保留所有行

-- 不去重，性能更好
SELECT name, '用户' AS 来源 FROM users
UNION ALL
SELECT name, '商品' AS 来源 FROM products;

-- 如果确定没有重复或不关心重复，优先用 UNION ALL

UNION 规则

-- 规则 1：列数必须相同
SELECT id, name FROM users
UNION ALL
SELECT id, name FROM products;  -- ✅ 两个 SELECT 都是 2 列

-- 规则 2：对应列的数据类型应兼容
SELECT id, name, email FROM users
UNION ALL
SELECT id, name, CAST(price AS CHAR) FROM products;  -- 类型不同时需显式转换

-- 规则 3：列名取自第一个 SELECT
SELECT name AS 名称, email AS 联系方式 FROM users
UNION ALL
SELECT name, CONCAT('¥', price) FROM products;
-- 结果集的列名是 "名称" 和 "联系方式"

实际用途：合并不同来源的操作日志

-- 将订单操作和用户操作合并成统一的活动流
SELECT
    o.created_at AS 时间,
    CONCAT('用户 ', u.name, ' 创建了订单 #', o.id) AS 活动描述,
    'order' AS 类型
FROM orders o
INNER JOIN users u ON o.user_id = u.id

UNION ALL

SELECT
    created_at AS 时间,
    CONCAT('新用户注册: ', name) AS 活动描述,
    'user' AS 类型
FROM users

ORDER BY 时间 DESC
LIMIT 20;

UNION 配合排序和分页

-- ORDER BY 和 LIMIT 作用于最终合并结果
(SELECT name, price FROM products WHERE price > 1000)
UNION ALL
(SELECT name, price FROM products WHERE price <= 1000)
ORDER BY price DESC
LIMIT 5;

-- 若要对单个 SELECT 排序/限制，需加括号
(SELECT name, price FROM products WHERE category = 'electronics' ORDER BY price DESC LIMIT 3)
UNION ALL
(SELECT name, price FROM products WHERE category = 'books' ORDER BY price DESC LIMIT 3);

💡 UNION vs UNION ALL 选择

UNION：需要去重时使用，内部执行排序去重操作，大数据量时较慢
UNION ALL：不需要去重时使用，直接合并结果集，性能更好
如果你能从逻辑上保证没有重复行（例如不同表的数据），直接用 UNION ALL

8 本章要点

🔗 INNER JOIN

返回两表的交集，只显示两边都匹配的行。多表 JOIN 可以链式连接

⬅️ LEFT JOIN

保留左表所有行，右表无匹配时填 NULL。WHERE b.id IS NULL 可查找不匹配行

✖️ CROSS JOIN

笛卡尔积：M 行 × N 行。实际用途包括生成日期范围、SKU 组合等

🔄 自连接

表与自身 JOIN，通过不同别名区分。适用于层级关系、同表配对等场景

📦 子查询

标量子查询、IN、EXISTS、FROM 派生表。能用 JOIN 替代时优先用 JOIN

📋 UNION

纵向合并结果集。UNION 去重，UNION ALL 保留全部，后者性能更好

⚠️ LEFT JOIN 陷阱

WHERE 条件会过滤 NULL 行导致退化为 INNER JOIN，额外过滤条件应放在 ON 子句

🏷️ 别名

多表 JOIN 时为每张表取短别名（u, o, p），大幅提升 SQL 的可读性

下一章预告：第六章将讲解聚合与分组——GROUP BY、聚合函数（COUNT/SUM/AVG/MAX/MIN）以及强大的窗口函数。掌握了 JOIN 再加上聚合分组，就能写出完整的数据分析报表。