一文搞懂MySQL的左、右、内、外连接

大家好，欢迎来到IT知识分享网。

一、前言

1、MySQL中的左连接、右连接、内连接和全外连接是用于多表关联查询的核心操作。

2、假设现有student表，结构如下：

3、score表结构如下：

二、内连接（INNER JOIN）

1、定义：返回两个表中完全匹配的行，即只保留两个表连接字段值相等的行。

2、示例场景：查询所有有选课记录的学生及其课程成绩。

SELECT * FROM student INNER JOIN score ON student.student_id = score.student_id;

3、结果特点：仅输出学生表和成绩表中student_id完全匹配的行，未选课的学生不会出现。

4、查询结果：

三、左连接（LEFT JOIN / LEFT OUTER JOIN）

1、定义：以左表为基准，返回左表所有行，右表只显示匹配的行，不匹配的字段以NULL填充。

2、示例场景：列出所有学生及其选课成绩（包括未选课的学生）。

SELECT * FROM student LEFT JOIN score ON student.student_id = score.student_id;

3、结果特点：学生表数据完整，未选课学生的成绩字段为NULL。

4、查询结果：

四、右连接（RIGHT JOIN / RIGHT OUTER JOIN）

1、定义：以右表为基准，返回右表所有行，左表只显示匹配的行，不匹配的字段以NULL填充。

2、示例场景：列出所有课程成绩对应的学生信息（包括未关联到学生的成绩）。

SELECT * FROM student RIGHT JOIN score ON student.student_id = score.student_id;

3、结果特点：成绩表数据完整，无效学生ID的成绩对应的学生字段为NULL。

4、查询结果：

五、全外连接（FULL OUTER JOIN）

1、定义：返回两个表的所有行，不匹配的字段以NULL填充。

2、示例场景：MySQL不直接支持FULL JOIN，需通过LEFT JOIN和RIGHT JOIN联合查询实现。

SELECT * FROM student LEFT JOIN score ON student.student_id = score.student_id UNION #该关键字的作用：合并两个select语句的查询结果集 SELECT * FROM student RIGHT JOIN score ON student.student_id = score.student_id; 

3、结果特点：包含所有学生和成绩记录，未匹配部分用NULL填充。

4、查询结果：

六、总结

七、拓展

1、性能提示：内连接通常效率最高，建议优先使用。避免对大表使用全外连接，可能引发性能问题。

2、NULL处理：外连接中NULL字段需用COALESCE()或IFNULL()处理，例如：

SELECT student.s_name, COALESCE(score, 0) FROM student LEFT JOIN score ON student.s_id = score.s_id;

3、自连接（Self Join）：同一表的关联查询，如查询员工及其上级

SELECT a.emp_name, b.emp_name AS manager FROM employees a LEFT JOIN employees b ON a.manager_id = b.emp_id;

4、具体应用时需结合业务需求选择连接方式，合理设计索引可显著提升多表查询效率。

5、具体应用时需结合业务需求选择连接方式，合理设计索引可显著提升多表查询效率。

6、如何区分左右表：

a、左连接会从左表（LEFT JOIN左侧的表）中返回所有的行，即使在右表（LEFT JOIN右侧的表）中没有匹配的行。如果右表中没有匹配的行，则结果中右表的部分会包含NULL值。

在这个例子中，table1是左表，table2是右表。

b、右连接会从右表（RIGHT JOIN右侧的表）中返回所有的行，即使在左表（RIGHT JOIN左侧的表）中没有匹配的行。如果左表中没有匹配的行，则结果中左表的部分会包含NULL值。

在这个例子中，table1是左表，但在右连接中我们通常称其为“右侧表”，而table2是右表，我们通常称其为“左侧表”。但从概念上讲，这里的“左右”是基于JOIN语句的书写位置来定义的。