一篇文章速通MySQL的多种连接方式 - 详解

1. 连接操作的基本概念

连接的本质

连接操作的核心是将两个或多个表中的数据基于某种关联条件组合在一起，形成一个新的结果集。

2. 各种连接类型的详细解析

2.1 INNER JOIN（内连接）- 只取交集

将两表作为参考对象，根据on后给出的两表的条件将两表连接起来。结果则是两表同时满足on后的条件的部分才会列出。

实际应用场景：

查询有部门的员工信息
查询已下单的客户信息
查询有成绩的学生信息

2.2 LEFT JOIN（左连接）- 保留左表全部

左连接以左表为基准，根据ON后给出的两表的条件将两表连接起来。结果会将左表所有的查询信息列出，而右表只列出ON后条件与左表满足的部分。如果右表没有匹配的记录，则右表字段显示为NULL。左连接全称为左外连接，是外连接的一种。

关键特点：

左表的每一条记录都会出现在结果中
右表可能有多条记录匹配左表的一条记录（一对多关系）
右表不匹配时，相关字段填充NULL

示例:

力扣175题，报告 Person 表中每个人的姓、名、城市和州。如果 personId 的地址不在 Address 表中，则报告为 null 。以任意顺序返回结果表。

SELECT
      Person.lastName,Person.firstName,Address.city,Address.state
FROM
      Person
LEFT JOIN
      Address
ON
      Person.personId = Address.personId;

实际应用场景：

查询所有员工信息（包括没有部门的员工）
统计所有产品的销售情况（包括未销售的产品）
查询所有客户的订单信息（包括未下单的客户）

2.3 RIGHT JOIN（右连接）- 保留右表全部

右连接以右表为基准，根据ON后给出的两表的条件将两表连接起来。结果会将右表所有的查询信息列出，而左表只列出ON后条件与右表满足的部分。如果左表没有匹配的记录，则左表字段显示为NULL。右连接全称为右外连接，是外连接的一种。

实际应用场景：

查询所有部门信息（包括没有员工的部门）
统计所有类别的商品（包括没有商品的类别）
查询所有供应商的供货情况（包括未供货的供应商）

2.4 FULL JOIN（全连接）

全连接返回两个表的所有记录，相当于左连接和右连接的并集。MySQL不直接支持FULL JOIN，但可以通过UNION组合LEFT JOIN和RIGHT JOIN来实现。

实际应用场景：

需要同时查看两个表的完整信息
数据对比分析
数据完整性检查

2.5 CROSS JOIN（交叉连接）

交叉连接返回两个表的笛卡尔积，即左表的每一行与右表的每一行组合。

示例：

-- 创建示例表
CREATE TABLE colors (
    color_name VARCHAR(20)
);
CREATE TABLE sizes (
    size_name VARCHAR(20)
);
-- 交叉连接：生成所有颜色和尺寸的组合
SELECT c.color_name, s.size_name
FROM colors c
CROSS JOIN sizes s;

2.6 NATURAL JOIN （自然连接）

自然连接自动根据两个表中相同名称的列进行连接，不需要指定连接条件。

示例：

-- 假设两个表都有相同的列名 id 和 name
SELECT *
FROM table1
NATURAL JOIN table2;

2.7 多表连接

同时连接三个或更多表。

-- 连接三个表：订单、客户、产品
SELECT o.order_id, c.customer_name, p.product_name, o.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

2.8 使用 USING 子句

当连接的两个表有相同名称的列时，可以使用 USING 简化语法。

SELECT 列名
FROM 表1
JOIN 表2 USING (共同列名);

示例：

-- 使用 USING 替代 ON
SELECT e.name, d.name
FROM employees e
JOIN departments d USING (department_id);

2.9 不等值连接

使用非等值条件进行连接。

-- 创建工资等级表
CREATE TABLE salary_grades (
    grade VARCHAR(10),
    min_salary INT,
    max_salary INT
);
-- 不等值连接：确定每个员工的工资等级
SELECT e.name, e.salary, sg.grade
FROM employees e
JOIN salary_grades sg ON e.salary BETWEEN sg.min_salary AND sg.max_salary;

3. 力扣题目练习

3.1 找出连续出现的数字（第180题）

题目描述：

找出所有至少连续出现三次的数字。

返回的结果表中的数据可以按 任意顺序 排列。

结果格式如下面的例子所示：

-- 使用自连接
SELECT DISTINCT l1.num AS ConsecutiveNums
FROM  Logs l1
join Logs l2 on l1.id = l2.id -1 AND l1.num = l2.num
join Logs l3 on l1.id = l3.id -2 AND l1.num = l3.num

3.2 超过经理收入的员工（第181题）

题目描述：

编写解决方案，找出收入比经理高的员工。以 任意顺序 返回结果表。结果格式如下所示。

SELECT a.name AS Employee
FROM Employee a,Employee b
WHERE a.managerId = b.id
AND a.salary > b.salary

3.3 上升的温度（第197题）

题目描述：

编写解决方案，找出与之前（昨天的）日期相比温度更高的所有日期的 id 。

返回结果 无顺序要求 。

SELECT b.id
FROM Weather a,Weather b
WHERE a.Temperature < b.Temperature AND DATEDIFF (a.RecordDate,b.RecordDate) = -1

3.4 部门工资前三高的所有员工(第185题)

题目描述：

公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的不同工资中 排名前三 。

编写解决方案，找出每个部门中 收入高的员工 。

以 任意顺序 返回结果表。

返回结果格式如下所示。

使用 JOIN 和子查询，公司里前 3 高的薪水意味着有不超过 3 个工资比这些值大。

SELECT d.name as Department,e1.name as Employee,e1.salary AS Salary
FROM Employee e1
join Department d on  e1.departmentId = d.id
WHERE 3 >(
    SELECT count(DISTINCT e2.Salary)
    FROM Employee e2
    WHERE e2.salary > e1.salary
    AND e1.departmentId =  e2.departmentId
)

3.5 体育馆的人流量（第601题）

题目描述：

编写解决方案找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。

返回按 visit_date升序排列 的结果表。

查询结果格式如下所示。

SELECT distinct s1.*
FROM Stadium s1, Stadium s2, Stadium s3
where(
    (s1.id = s2.id-1 AND s1.id = s3.id-2) or
    (s1.id = s2.id-1 AND s1.id = s3.id+1) or
    (s1.id = s2.id+1 AND s1.id = s3.id+2 )
)
AND s1.people >= 100
AND s2.people >= 100
AND s3.people >= 100
ORDER BY s1.visit_date;

通过自连接把Stadium表当成三条独立记录s1、s2、s3，让它们按id相邻排成“三连号”，从而把任何连续三条记录都拉出来，再统一过滤这三条记录的people都不低于100，最后去重并按s1.visit_date排序，得到的就是“人数连续三天及以上破百”的那些天里，所有满足条件的日期记录。

posted @ 2025-12-25 15:25 clnchanpin 阅读(73) 评论(0) 收藏举报

刷新页面返回顶部