MySql的各种语句（最详细）-5（七种join操作）

#七种join操作

#情况1：中图：
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

#情况2：左上图：左外连接
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;

#情况3：右上图：右外连接
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

#情况4：左中图：
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;

#情况5：右中图：
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

#情况6：左下图：满外连接
#左中图：
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
#右上图
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;

#情况7：右下图：
#左中图：
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
#右中图：
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;