mysql菜鸟教程

首页 >> mysql菜鸟教程

专栏导航

11.1 连接查询的概念

在真实的数据库应用中，数据往往不是孤立地存储在一张表中。例如，学生信息存放在 students 表，班级信息存放在 classes 表。当我们需要查询“张三所在的班级名称”时，就必须从两张表中同时获取数据——学生表提供姓名，班级表提供班级名称。这种从多个表中联合检索数据的操作，就是连接查询（JOIN）。

一、为什么需要连接查询？

规范化设计将数据拆分到不同的表中以减少冗余，但业务查询常常需要将这些分散的信息重新组合起来。连接查询正是实现这种“重组”的工具。

例如，我们有如下两张表：

students 表（学生基本信息）

student_id	name	class_id
1	张三	101
2	李四	102
3	王五	101

classes 表（班级信息）

class_id	class_name
101	计算机1班
102	软件2班

如果我们想显示每个学生的姓名及其班级名称，就需要将两张表通过 class_id 连接起来。

二、连接的基本原理

连接的本质是将两张表的记录按照指定的连接条件组合起来。如果不加任何条件，将两张表的每条记录互相组合，会产生笛卡尔积。例如，上面的 students 表有3条记录，classes 表有2条记录，笛卡尔积就有 3×2 = 6 条记录：

student_id	name	class_id	class_id (来自classes)	class_name
1	张三	101	101	计算机1班
1	张三	101	102	软件2班
2	李四	102	101	计算机1班
2	李四	102	102	软件2班
3	王五	101	101	计算机1班
3	王五	101	102	软件2班

这里面很多组合是毫无意义的——比如张三（class_id=101）却和“软件2班”（class_id=102）组合在一起。因此我们需要连接条件来筛选出有意义的组合，通常使用主键-外键关系作为条件：students.class_id = classes.class_id。加上条件后，结果就只有 3 条有效记录：

student_id	name	class_id	class_id	class_name
1	张三	101	101	计算机1班
2	李四	102	102	软件2班
3	王五	101	101	计算机1班

三、连接查询的类型

根据业务需求的不同，SQL 提供了多种连接方式。以下是 MySQL 中常用的几种：

1. 内连接（INNER JOIN）

内连接只返回两张表中满足连接条件的记录。如果某张表的记录在另一张表中没有匹配，则不会出现在结果中。

SELECT students.name, classes.class_name
FROM students
INNER JOIN classes ON students.class_id = classes.class_id;

结果如上表所示，所有学生都显示班级（假设每个学生都有班级）。如果某个学生没有分配班级（class_id 为 NULL），则该学生不会出现在内连接的结果中。

2. 左连接（LEFT JOIN）

左连接返回左表中的所有记录，即使右表中没有匹配。如果右表没有匹配，则相应列显示为 NULL。

SELECT students.name, classes.class_name
FROM students
LEFT JOIN classes ON students.class_id = classes.class_id;

假设李四的 class_id 为 NULL，左连接结果中李四仍会出现，但班级名称为 NULL。

3. 右连接（RIGHT JOIN）

右连接与左连接相反，返回右表中的所有记录，左表没有匹配时显示 NULL。通常可以用左连接调换表顺序实现相同效果，因此右连接使用较少。

SELECT students.name, classes.class_name
FROM students
RIGHT JOIN classes ON students.class_id = classes.class_id;

如果某个班级没有学生，该班级仍会出现在结果中，但学生姓名为 NULL。

4. 全外连接（FULL OUTER JOIN）

全外连接返回两张表中的所有记录，无论是否匹配，不匹配的一侧用 NULL 填充。MySQL 不直接支持 FULL OUTER JOIN，但可以通过 LEFT JOIN UNION RIGHT JOIN 模拟。

5. 交叉连接（CROSS JOIN）

交叉连接返回两张表的笛卡尔积，即所有可能的组合。通常用于生成测试数据或特定算法，日常业务中很少使用。

SELECT * FROM students CROSS JOIN classes;

四、连接查询的语法

ANSI SQL 标准推荐使用显式的 JOIN 语法，即 ... JOIN ... ON ...。这种方式可读性强，易于维护。另一种老式语法是在 WHERE 子句中写连接条件，例如：

SELECT students.name, classes.class_name
FROM students, classes
WHERE students.class_id = classes.class_id;

这种语法将连接条件和筛选条件混在一起，容易混淆，且容易遗漏连接条件导致笛卡尔积，因此不推荐使用。在现代开发中，应始终使用显式 JOIN。

五、连接查询的执行顺序

理解连接查询的逻辑执行顺序有助于写出更高效的查询。虽然数据库优化器可能实际执行顺序不同，但从逻辑上讲：

首先计算 FROM 子句中所有表的笛卡尔积。
应用 ON 条件筛选满足连接条件的行。
如果是外连接，则保留主表中未匹配的行，并补 NULL。
应用 WHERE 子句中的条件进一步过滤。
进行 GROUP BY、HAVING、ORDER BY、LIMIT 等后续操作。

六、连接查询的注意事项

连接条件必须明确：确保使用正确的列进行连接，通常是主键和外键。
避免不必要的笛卡尔积：忘记写连接条件会导致结果急剧膨胀，严重影响性能。
为连接列建立索引：在大表上执行连接查询时，应在连接列上创建索引，以大幅提升查询速度。
选择合适的连接类型：根据业务需求决定使用内连接还是外连接。
表别名：当表名较长或需要自连接时，建议使用表别名，使查询更简洁。

SELECT s.name, c.class_name
FROM students AS s
LEFT JOIN classes AS c ON s.class_id = c.class_id;

七、小结

连接查询是从多张表中组合数据的核心操作。
通过连接条件（通常基于主外键关系）将相关记录关联起来。
主要连接类型：内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）、全外连接（FULL OUTER JOIN）、交叉连接（CROSS JOIN）。
推荐使用显式的 JOIN ... ON ... 语法，避免在 WHERE 子句中写连接条件。
连接查询是后续学习复杂查询（如多表连接、子查询）的基础，务必熟练掌握。

在接下来的几节中，我们将分别深入探讨各种连接的具体用法、示例和性能优化技巧。