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

一、前言

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

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

• score表结构如下：
  

---

二、内连接（INNER JOIN）

• 定义：返回两个表中完全匹配的行，即只保留两个表连接字段值相等的行。
• 示例场景：查询所有有选课记录的学生及其课程成绩。

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

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

---

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

• 定义：以左表为基准，返回左表所有行，右表只显示匹配的行，不匹配的字段以NULL填充。
• 示例场景：列出所有学生及其选课成绩（包括未选课的学生）。

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

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

---

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

• 定义：以右表为基准，返回右表所有行，左表只显示匹配的行，不匹配的字段以NULL填充。
• 示例场景：列出所有课程成绩对应的学生信息（包括未关联到学生的成绩）。

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

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

---

五、全外连接（FULL OUTER JOIN）

• 定义：返回两个表的所有行，不匹配的字段以NULL填充。
• 示例场景：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;

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

---

六、总结

---

七、拓展

• 性能提示：
  • 内连接通常效率最高，建议优先使用。
  • 避免对大表使用全外连接，可能引发性能问题。
• NULL处理：
  • 外连接中NULL字段需用COALESCE()或IFNULL()处理，例如：

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

• 自连接（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;

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