连接类型详解
连接类型详解
想象你经营一家咖啡店，每天要处理三种情况：既有会员又消费了的顾客（两表都有数据）、有会员但没消费的顾客（左表有、右表没有）、消费了但没注册会员的顾客（右表有、左表没有）。SQL提供了不同类型的JOIN来精确处理这三种（以及更多）场景。
1. 核心概念
六种连接类型总览
| 连接类型 | 说明 | 返回结果 |
|---|---|---|
| INNER JOIN | 内连接 | 只返回两表都匹配的行 |
| LEFT JOIN | 左连接 | 返回左表所有行 + 右表匹配的行（不匹配则为NULL） |
| RIGHT JOIN | 右连接 | 返回右表所有行 + 左表匹配的行（不匹配则为NULL） |
| FULL OUTER JOIN | 全外连接 | 返回两表所有行（不匹配则为NULL） |
| CROSS JOIN | 交叉连接 | 返回两表的笛卡尔积（所有组合） |
| SELF JOIN | 自连接 | 表与自身连接 |
用韦恩图理解JOIN
用两个圆圈A和B表示两张表：
┌───────┐ ┌───────┐
│ A │ │ B │
│ │ │ │
│ ┌───┼───┼───┐ │
│ │ A ∩ B │ │ │
│ └───┼───┼───┘ │
│ │ │ │
└───────┘ └───────┘
INNER JOIN → A ∩ B（交集）
LEFT JOIN → A（全部A + A∩B）
RIGHT JOIN → B（全部B + A∩B）
FULL JOIN → A ∪ B（并集）
CROSS JOIN → A × B（所有组合）
INNER JOIN（内连接）
只保留两表中都匹配的行。上一课已详细介绍，此处不再赘述。
SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
LEFT JOIN（左连接）
返回左表所有行，右表没有匹配的列显示为NULL。左表是FROM后面的第一张表。
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
输出（注意周九的department为NULL）：
name department
------ ----------
张三 技术部
李四 技术部
王五 市场部
赵六 财务部
钱七 技术部
孙八 市场部
周九 NULL
吴十 财务部
RIGHT JOIN（右连接）
返回右表所有行，左表没有匹配的列显示为NULL。右表是JOIN后面那张表。
SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;
-- 等价于 RIGHT JOIN departments d ON ...
SELECT e.name, d.name AS department
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id;
FULL OUTER JOIN（全外连接）
返回两表所有行，没有匹配的地方显示为NULL。相当于LEFT JOIN和RIGHT JOIN的并集。
-- 语法（MySQL/PostgreSQL/SQL Server支持）
SELECT e.name, d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;
LEFT JOIN + UNION + RIGHT JOIN来模拟：
-- 用 LEFT JOIN UNION RIGHT JOIN 模拟 FULL OUTER JOIN
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
UNION
SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;
同样需要注意SQLite不支持RIGHT JOIN，实际在SQLite中可以这样实现：
-- SQLite中的FULL OUTER JOIN等价写法
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
UNION
SELECT e.name, d.name AS department
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id;
💡 提示：实际开发中，FULL OUTER JOIN使用频率较低。大多数场景用LEFT JOIN就能满足需求，建议优先考虑LEFT JOIN。
CROSS JOIN（交叉连接）
返回两表的笛卡尔积——左表每一行与右表每一行的所有组合。结果行数 = 左表行数 × 右表行数。
SELECT e.name, d.name AS department
FROM employees e
CROSS JOIN departments d;
假设employees有8行、departments有4行，结果将有 8 × 4 = 32 行。
SELF JOIN（自连接）
表与自身连接。常用于查询"层级关系"或"同一表中的配对"，比如员工和他们的上级。
-- 假设employees表有一个manager_id字段
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
e和m），否则SQL无法区分两次引用。
JOIN选择决策树
面对业务需求时，按以下逻辑选择连接类型：
需要连接两张表？
├── 只要匹配的数据 → INNER JOIN
├── 保留左表全部数据 → LEFT JOIN
├── 保留右表全部数据 → RIGHT JOIN
├── 保留两表全部数据 → FULL OUTER JOIN
├── 需要所有组合 → CROSS JOIN
└── 表与自身关联 → SELF JOIN
2. 基本语法/用法
LEFT JOIN 语法
SELECT 列名
FROM 表A
LEFT JOIN 表B ON 表A.列 = 表B.列;
LEFT OUTER JOIN和LEFT JOIN是完全等价的，OUTER关键字可以省略。
ON条件只影响右表的匹配，左表的所有行都会被保留。
RIGHT JOIN 语法
SELECT 列名
FROM 表A
RIGHT JOIN 表B ON 表A.列 = 表B.列;
CROSS JOIN 语法
SELECT 列名
FROM 表A
CROSS JOIN 表B;
-- CROSS JOIN 不需要 ON 条件
SELF JOIN 语法
SELECT a.列, b.列
FROM 表A a
JOIN 表A b ON a.某列 = b.某列;
示例：查找没有部门的员工（难度⭐）
用LEFT JOIN找出所有员工，包括那些没有分配部门的：
SELECT e.name AS employee,
COALESCE(d.name, '未分配') AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
输出：
employee department
-------- ----------
张三 技术部
李四 技术部
王五 市场部
赵六 财务部
钱七 技术部
孙八 市场部
周九 未分配
吴十 财务部
COALESCE(d.name, '未分配')表示：如果d.name是NULL，就显示"未分配"。
示例：查找没有订单的商品（难度⭐⭐）
商品表中有8个商品，但不是每个商品都有订单。用LEFT JOIN找出"零销量"的商品：
SELECT p.name AS product, p.category, p.price,
COUNT(o.id) AS order_count
FROM products p
LEFT JOIN orders o ON p.id = o.product_id
GROUP BY p.id, p.name, p.category, p.price
HAVING COUNT(o.id) = 0;
输出（取决于数据，某些商品可能没有订单记录）：
product category price order_count
------- -------- -------- -----------
Mac Mini 电脑 4499.00 0
GROUP BY聚合，最后用HAVING过滤出订单数为0的商品。
示例：员工与上级的自连接（难度⭐⭐⭐）
假设employees表增加了manager_id字段，查询每个员工及其上级：
-- 先添加manager_id字段和数据
ALTER TABLE employees ADD COLUMN manager_id INTEGER;
UPDATE employees SET manager_id = 5 WHERE id IN (1, 2, 3, 6);
UPDATE employees SET manager_id = NULL WHERE id = 5;
-- 自连接查询
SELECT e.name AS employee,
COALESCE(m.name, '无上级') AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
输出：
employee manager
-------- --------
张三 钱七
李四 钱七
王五 钱七
赵六 无上级
钱七 无上级
孙八 钱七
周九 无上级
吴十 无上级
e代表"作为员工的自己"，m代表"作为上级的自己"。通过manager_id = m.id建立层级关系。
3. 常见应用场景
场景一：生成部门-员工矩阵报表
用CROSS JOIN生成"每个部门 × 每个员工"的完整矩阵：
SELECT d.name AS department, e.name AS employee
FROM departments d
CROSS JOIN employees e
WHERE e.department_id IS NOT NULL
ORDER BY d.name, e.name;
这在生成"全员参与的打卡表"等场景中很实用。
场景二：查找同部门的员工配对
用SELF JOIN找出同一部门的所有员工配对：
SELECT e1.name AS employee1,
e2.name AS employee2,
d.name AS department
FROM employees e1
INNER JOIN employees e2 ON e1.department_id = e2.department_id
AND e1.id < e2.id
INNER JOIN departments d ON e1.department_id = d.id;
输出：
employee1 employee2 department
--------- --------- ----------
张三 李四 技术部
张三 钱七 技术部
李四 钱七 技术部
王五 孙八 市场部
赵六 吴十 财务部
e1.id < e2.id避免了重复配对（如"张三-李四"和"李四-张三"只保留一个）和自配对（如"张三-张三"）。
❓ 常见问题
Q：LEFT JOIN和INNER JOIN什么时候结果一样？ A： 当左表中没有NULL的外键值时（即所有行都能在右表中找到匹配），LEFT JOIN和INNER JOIN的结果完全一致。但LEFT JOIN会多保留那些右表没有匹配的行。
Q：SQLite不支持RIGHT JOIN和FULL OUTER JOIN怎么办？ A： RIGHT JOIN可以通过调换表的顺序改用LEFT JOIN实现。FULL OUTER JOIN可以用LEFT JOIN + UNION + LEFT JOIN模拟。实际上RIGHT JOIN在实际开发中也很少使用，大多数情况LEFT JOIN就够了。
Q：CROSS JOIN会不会很慢？ A： 如果两张表分别有1000行，CROSS JOIN会产生100万行结果。所以使用时要非常小心，确保数据量可控。通常CROSS JOIN会配合WHERE条件使用，实际上等价于INNER JOIN。
Q：SELF JOIN有什么实际用途？ A： SELF JOIN常用于处理层级数据（如组织架构、评论回复、分类树），也用于查找"同组内的配对"（如同部门员工、同城市客户）。只要需要"同一张表内的行与行之间做比较"，就可能用到SELF JOIN。
📖 小节
- INNER JOIN只返回匹配行，LEFT JOIN保留左表全部，RIGHT JOIN保留右表全部
- FULL OUTER JOIN返回两表并集，但SQLite不支持，需用LEFT JOIN + UNION模拟
- CROSS JOIN返回笛卡尔积，数据量大时要谨慎使用
- SELF JOIN让表与自身连接，适合处理层级关系和同组配对
- 连接选择决策树：要匹配用INNER，要保留用LEFT/RIGHT，要全部用FULL，要组合用CROSS
- SQLite不支持RIGHT JOIN和FULL OUTER JOIN，但可以通过调换表顺序和UNION组合来等价实现
📝 作业
作业1（⭐）：编写查询，显示所有部门及其员工数量，包括没有员工的部门。结果按员工数量降序排列。
作业2（⭐⭐）：编写查询，找出所有"没有下过订单的客户"。假设我们需要一个customers表，可以先用orders表中的customer_name字段模拟——找出哪些客户只出现在orders表中，哪些商品从未被订购过。
作业3（⭐⭐⭐）：编写查询，用SELF JOIN找出"入职日期相邻的员工对"——即入职日期最接近的两位员工（提示：可以用julianday()函数计算日期差）。
下一课
👉 09-subquery - 子查询：学习在WHERE、FROM、SELECT中使用子查询，以及EXISTS/NOT EXISTS的用法！
