连接查询

连接查询（JOIN）是 SQL 的灵魂所在。在实际业务中，数据往往分散在多个关联表中，要获取完整信息就必须掌握连接查询。理解连接的本质，就是理解"表间关系"。

JOIN 的核心概念

什么是连接？

连接是将两个或多个表中的行结合起来的操作，基于这些表之间的共同字段（通常是外键关系）。

连接的类型对比

理解不同类型连接的最佳方式是通过韦恩图：

连接类型详解：

连接类型	说明	使用场景
`INNER JOIN`	只返回两表都有匹配的行	查询有关联的数据
`LEFT JOIN`	返回左表所有行，右表无匹配则为 NULL	查询左表为主，可能没有关联的数据
`RIGHT JOIN`	返回右表所有行，左表无匹配则为 NULL	较少使用，通常用 LEFT JOIN 替代
`CROSS JOIN`	笛卡尔积，两表每行组合	生成所有可能的组合
`SELF JOIN`	表自己连接自己	处理层级数据、比较同一表中的行

LEFT JOIN 的经典模式

LEFT JOIN ... WHERE right.id IS NULL 是一个非常重要的模式，用于找出"在左表但不在右表"的记录：

-- 找出没有订单的客户
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;  -- 关键：右表的主键为 NULL 表示没有匹配

为什么这样可以工作？

LEFT JOIN 会返回左表的所有行
如果右表没有匹配，右表的所有列都是 NULL
WHERE o.id IS NULL 过滤出右表没有匹配的行
这比 NOT IN 或 NOT EXISTS 更直观

1378. 使用唯一标识码替换员工ID (Employees Unified ID)

题目描述

你有两张表：

Employees: 包含 id (主键) 和 name。
EmployeeUNI: 包含 id 和 unique_id (两者共同作为主键)。

请编写一个解决方案，展示每位员工的姓名及其对应的 唯一标识码 (unique_id)。如果某位员工没有唯一标识码，则展示为 null。

解题思路

这道题考查 LEFT JOIN 的基本使用。

分析过程：

确定主表：题目要求展示"每位员工"的信息，所以 Employees 是主表（左表）
选择连接类型：员工可能没有唯一标识码，需要用 LEFT JOIN 保证所有员工都出现在结果中
连接条件：两个表通过 id 字段关联

为什么不用 INNER JOIN？

如果使用 INNER JOIN，那些没有唯一标识码的员工就不会出现在结果中，这不符合"展示每位员工"的要求。

SQL 实现 (MySQL)

-- 方法一：LEFT JOIN + ON
SELECT unique_id, name 
FROM Employees e
LEFT JOIN EmployeeUNI u ON e.id = u.id;

-- 方法二：LEFT JOIN + USING（当连接字段名相同时）
SELECT unique_id, name 
FROM Employees LEFT JOIN EmployeeUNI USING(id);

代码解析：

LEFT JOIN：保证左表（Employees）的所有行都出现在结果中
ON e.id = u.id：指定连接条件
USING(id)：当两个表的连接字段名相同时的简写形式

1068. 产品销售分析 I (Product Sales Analysis I)

题目描述

你有两张表：

Sales: 包含 sale_id, product_id, year, quantity, price。
Product: 包含 product_id 和 product_name。

请编写一个解决方案，查询 Sales 表中每一笔销售记录对应的 产品名称 (product_name)、年份 (year) 和 价格 (price)。

解题思路

这道题考查 INNER JOIN 的基本使用。

分析过程：

理解需求：查询每笔销售记录的产品名称，需要从 Product 表获取名称
选择连接类型：销售记录一定有对应的产品，用 INNER JOIN 即可
连接条件：product_id 字段

INNER JOIN vs LEFT JOIN 的选择：

如果需要"所有销售记录"，用 INNER JOIN 或 LEFT JOIN 都可以
如果需要"所有产品（包括没卖出去的）"，必须用 RIGHT JOIN（以 Product 为右表）
通常以"主表"的概念来选择：查询的主实体是什么？

SQL 实现 (MySQL)

-- 方法：INNER JOIN
SELECT product_name, year, price 
FROM Sales s 
JOIN Product p ON s.product_id = p.product_id;

-- 方法二：使用 USING
SELECT product_name, year, price 
FROM Sales s JOIN Product p USING(product_id);

1581. 进店却未进行过交易的顾客 (Customer Visits with No Transactions)

题目描述

你有两张表：

Visits: 包含 visit_id 和 customer_id。记录了顾客进店的情况。
Transactions: 包含 transaction_id, visit_id, amount。记录了每笔交易及其对应的进店记录。

请编写一个解决方案，找出所有 进店了但没有进行交易 的顾客。你需要展示这些顾客的 customer_id 以及他们 未进行交易的次数。

解题思路

这道题考查 LEFT JOIN + IS NULL 的经典模式。

分析过程：

理解需求：找出进店但没有交易的顾客，即"在 Visits 但不在 Transactions"
连接方式：Visits LEFT JOIN Transactions，保留所有进店记录
过滤条件：transaction_id IS NULL 表示没有交易
聚合统计：按顾客分组，统计未交易的次数

经典的"找差集"模式：

SQL 实现 (MySQL)

SELECT customer_id, COUNT(*) AS count_no_trans 
FROM Visits v 
LEFT JOIN Transactions t ON v.visit_id = t.visit_id 
WHERE t.transaction_id IS NULL 
GROUP BY customer_id;

代码解析：

LEFT JOIN：保留所有进店记录
WHERE t.transaction_id IS NULL：过滤出没有交易的记录
COUNT(*)：统计每个顾客未交易的次数
GROUP BY customer_id：按顾客分组

197. 上升的温度 (Rising Temperature)

题目描述

Weather 表记录了每天的温度信息：

id: 日志 ID。
recordDate: 日期。
temperature: 当天的温度。

编写一个解决方案，找出所有 温度比前一天高 的日期的 ID。

解题思路

这道题考查 自连接（Self Join） 的应用。

什么是自连接？

自连接是指一个表和自己进行连接。这在以下场景非常有用：

比较同一表中不同行的数据
处理层级数据（如员工-经理关系）
查找相邻记录

分析过程：

比较相邻日期：需要将表连接两次，一次代表"今天"，一次代表"昨天"
连接条件：日期相差 1 天
过滤条件：今天的温度高于昨天

日期差函数：

-- MySQL
DATEDIFF(date1, date2)  -- 返回 date1 - date2 的天数

-- 示例
DATEDIFF('2024-01-15', '2024-01-10')  -- 返回 5
DATEDIFF('2024-01-10', '2024-01-15')  -- 返回 -5

SQL 实现 (MySQL)

-- 方法：自连接 + DATEDIFF
SELECT w1.id 
FROM Weather w1 
JOIN Weather w2 ON DATEDIFF(w1.recordDate, w2.recordDate) = 1 
WHERE w1.temperature > w2.temperature;

代码解析：

FROM Weather w1 JOIN Weather w2：自连接，w1 代表今天，w2 代表昨天
DATEDIFF(w1.recordDate, w2.recordDate) = 1：w1 的日期比 w2 晚 1 天
w1.temperature > w2.temperature：今天的温度高于昨天

使用窗口函数的替代方案：

-- 使用 LAG 函数获取前一天的温度
SELECT id
FROM (
    SELECT id, temperature, 
           LAG(temperature) OVER (ORDER BY recordDate) AS prev_temp
    FROM Weather
) t
WHERE temperature > prev_temp;

1661. 每台机器的进程平均运行时间 (Average Time of Process per Machine)

题目描述

Activity 表记录了工厂中每台机器处理不同进程的情况：

machine_id: 机器 ID。
process_id: 进程 ID。
activity_type: 枚举 ('start', 'end')。
timestamp: 发生时间。

每台机器的每个进程都有配套的 'start' 和 'end' 记录。进程的 运行时间 为 end 时间与其对应的 start 时间之差。请编写一个解决方案，计算每台机器执行进程的 平均时间，并将结果保留 3 位小数。

解题思路

这道题考查 条件聚合 和 分组计算。

数据结构分析：

同一个进程的 start 和 end 是两条不同的记录，需要将它们合并计算时间差。

两种解决思路：

自连接法：将表连接两次，分别匹配 start 和 end
条件聚合法：使用 SUM(IF(...)) 在聚合中分别处理 start 和 end

条件聚合技巧：

-- start 时间取负，end 时间取正，求和就是时间差
SUM(IF(activity_type = 'start', -timestamp, timestamp))
-- 这样 start 和 end 的差就是运行时间

SQL 实现 (MySQL)

-- 方法：条件聚合
SELECT machine_id, 
       ROUND(SUM(IF(activity_type = 'start', -timestamp, timestamp)) / COUNT(DISTINCT process_id), 3) AS processing_time 
FROM Activity 
GROUP BY machine_id;

代码解析：

IF(activity_type = 'start', -timestamp, timestamp)：start 取负，end 取正
SUM(...)：同一进程的 start（负）+ end（正）= 运行时间
COUNT(DISTINCT process_id)：每台机器的进程数
ROUND(..., 3)：保留 3 位小数

577. 员工奖金 (Employee Bonus)

题目描述

你有两张表：

Employee: 包含员工姓名 name 和 ID empId。
Bonus: 包含 empId 和对应的奖金 bonus。

查询所有 奖金少于 1000 的员工姓名及其奖金数额。注意，没有奖金的员工（奖金为 NULL）也应该被包含在结果中。

解题思路

这道题考查 LEFT JOIN + 复合条件。

分析过程：

确定主表：查询员工信息，Employee 是主表
连接方式：LEFT JOIN Bonus，保证没有奖金的员工也出现
过滤条件：bonus < 1000 OR bonus IS NULL

为什么条件要包含 IS NULL？

NULL < 1000 的结果是 UNKNOWN，不是 TRUE
如果只用 bonus < 1000，没有奖金的员工会被过滤掉
题目要求"没有奖金的员工也应该被包含"

SQL 实现 (MySQL)

SELECT name, bonus 
FROM Employee e 
LEFT JOIN Bonus b ON e.empId = b.empId 
WHERE bonus < 1000 OR bonus IS NULL;

1280. 学生们参加各科测试的次数 (Students and Examinations)

题目描述

你有三张表：

Students: 包含 student_id (主键) 和 student_name。
Subjects: 包含 subject_name (主键)。
Examinations: 记录了学生参加特定科目考试的记录，不含主键，可能存在多条相同记录。

查询出 每个学生 参加 每一门科目 测试的次数。结果需按 student_id 和 subject_name 升序排列。即使某学生没参加某门考试，次数也应显示为 0。

解题思路

这道题考查 CROSS JOIN + LEFT JOIN 的组合使用。

核心难点：如何保证"每个学生 x 每门科目"的组合都出现？

解决方案：

生成所有组合：用 CROSS JOIN 生成学生和科目的所有组合
左连接考试记录：用 LEFT JOIN 连接考试表
统计次数：COUNT(e.subject_name) 统计考试次数（用字段而非 *，因为 NULL 不计数）

CROSS JOIN 的作用：

SQL 实现 (MySQL)

SELECT s.student_id, s.student_name, sub.subject_name, 
       COUNT(e.subject_name) AS attended_exams 
FROM Students s 
CROSS JOIN Subjects sub 
LEFT JOIN Examinations e ON s.student_id = e.student_id AND sub.subject_name = e.subject_name 
GROUP BY s.student_id, sub.subject_name 
ORDER BY s.student_id, sub.subject_name;

代码解析：

CROSS JOIN Subjects：生成学生和科目的所有组合
LEFT JOIN Examinations：连接考试记录，没有考试的显示 NULL
COUNT(e.subject_name)：统计考试次数（NULL 不计数，所以没考试的为 0）
注意 LEFT JOIN 的条件有两个：student_id 和 subject_name 都要匹配

570. 至少有5名直接下属的经理 (Managers with at Least 5 Direct Reports)

题目描述

在 Employee 表中，每一行包含员工的 ID, 姓名, 所在的部门以及他们的经理 ID (managerId)：

id: 员工 ID，主键。
name: 姓名。
managerId: 该员工经理的 ID。如果为 NULL，则该员工没有经理。

请编写一个解决方案，找出那些 至少有 5 名直接下属 的经理姓名。

解题思路

这道题考查 分组统计 + 子查询。

分析过程：

统计每个经理的下属数量：按 managerId 分组计数
筛选符合条件的经理 ID：HAVING COUNT(*) >= 5
获取经理姓名：用子查询的结果过滤员工表

SQL 实现 (MySQL)

-- 方法：子查询
SELECT name 
FROM Employee 
WHERE id IN (
    SELECT managerId 
    FROM Employee 
    GROUP BY managerId 
    HAVING COUNT(*) >= 5
);

1934. 确认率 (Confirmation Rate)

题目描述

用户的 确认率 是 confirmed 消息的数量除以请求的确认消息的总数。从未请求过确认消息的用户的确认率为 0 。查询每个用户的确认率，并保留两位小数。

解题思路

这道题考查 LEFT JOIN + 条件聚合。

核心技巧：

在 MySQL 中，布尔表达式在数值上下文中会转换为 1 或 0：

action = 'confirmed' 结果为 TRUE 时返回 1，FALSE 时返回 0
AVG(布尔表达式) 可以直接计算比例

处理 NULL 的情况：

从未请求过确认的用户，AVG 结果为 NULL
使用 IFNULL(..., 0) 将 NULL 转换为 0

SQL 实现 (MySQL)

SELECT s.user_id, 
       ROUND(IFNULL(AVG(c.action = 'confirmed'), 0), 2) AS confirmation_rate 
FROM Signups s 
LEFT JOIN Confirmations c USING(user_id) 
GROUP BY s.user_id;

代码解析：

LEFT JOIN：保证所有注册用户都在结果中
c.action = 'confirmed'：布尔表达式，confirmed 返回 1，否则返回 0
AVG(...)：计算 1 的比例，即确认率
IFNULL(..., 0)：处理没有请求的用户

知识点总结

JOIN 类型速查

连接类型	语法	结果
内连接	`INNER JOIN`	只返回匹配的行
左连接	`LEFT JOIN`	左表全部 + 右表匹配（无匹配为 NULL）
右连接	`RIGHT JOIN`	右表全部 + 左表匹配（无匹配为 NULL）
交叉连接	`CROSS JOIN`	笛卡尔积
自连接	`表 AS a JOIN 表 AS b`	同一表自我连接

常见模式

找差集：LEFT JOIN ... WHERE right.id IS NULL
比较相邻行：自连接或窗口函数 LAG()
生成所有组合：CROSS JOIN
条件聚合：SUM(IF(...)), AVG(布尔表达式)

注意事项

选择正确的连接类型（以哪个表为主）
注意 NULL 值的处理
连接条件要完整，避免笛卡尔积
多表连接时注意性能，合理使用索引

JOIN 的核心概念​

什么是连接？​

连接的类型对比​

LEFT JOIN 的经典模式​

题目描述​

解题思路​

SQL 实现 (MySQL)​

题目描述​

解题思路​

SQL 实现 (MySQL)​

题目描述​

解题思路​

SQL 实现 (MySQL)​

题目描述​

解题思路​

SQL 实现 (MySQL)​

题目描述​

解题思路​

SQL 实现 (MySQL)​

题目描述​

解题思路​

SQL 实现 (MySQL)​

题目描述​

解题思路​

SQL 实现 (MySQL)​

题目描述​

解题思路​

SQL 实现 (MySQL)​

题目描述​

解题思路​

SQL 实现 (MySQL)​

知识点总结​

JOIN 类型速查​

常见模式​

注意事项​

JOIN 的核心概念

什么是连接？

连接的类型对比

LEFT JOIN 的经典模式

题目描述

解题思路

SQL 实现 (MySQL)

题目描述

解题思路

SQL 实现 (MySQL)

题目描述

解题思路

SQL 实现 (MySQL)

题目描述

解题思路

SQL 实现 (MySQL)

题目描述

解题思路

SQL 实现 (MySQL)

题目描述

解题思路

SQL 实现 (MySQL)

题目描述

解题思路

SQL 实现 (MySQL)

题目描述

解题思路

SQL 实现 (MySQL)

题目描述

解题思路

SQL 实现 (MySQL)

知识点总结

JOIN 类型速查

常见模式

注意事项