MySQL中其中7中JOIN方法_mysql 7中join实现-优快云博客

本文链接：https://blog.youkuaiyun.com/m0_64436820/article/details/134892615

本文详细介绍了SQL中的各种连接类型，包括左外连接、内连接、右外连接、左中连接、右中连接、满外连接（全连接）以及右下连接，通过示例展示了如何在employees表和departments表间进行数据关联查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

employees表

departments表

左外连接：

select emp.last_name, dept.department_name
from employees emp 
left join departments dept on emp.department_id = dept.department_id;

内连接：

SELECT emp.last_name, emp.employee_id, emp2.last_name manager_name, emp2.employee_id manager_id
from employees emp
INNER JOIN employees emp2 on emp.manager_id = emp2.employee_id;

右外连接：

select emp.last_name, dept.department_name
from employees emp 
right join departments dept on emp.department_id = dept.department_id;

左中连接：

select emp.last_name, dept.department_name
from employees emp 
left join departments dept on emp.department_id = dept.department_id
where dept.department_id is null;

右中连接：

select emp.last_name, dept.department_name
from employees emp 
right join departments dept on emp.department_id = dept.department_id
where emp.department_id is null;

满外连接(全连接) ：

select emp.last_name, dept.department_name
from employees emp 
left join departments dept on emp.department_id = dept.department_id
UNION ALL
select emp.last_name, dept.department_name
from employees emp 
right join departments dept on emp.department_id = dept.department_id
where emp.department_id is null;

右下连接：

select emp.last_name, dept.department_name
from employees emp 
left join departments dept on emp.department_id = dept.department_id
where dept.department_id is null
UNION ALL 
select emp.last_name, dept.department_name
from employees emp 
right join departments dept on emp.department_id = dept.department_id
where emp.department_id is null;