[打卡]李文静 多表查询

#左外连接、右外连接、满外链接

#满外链接

SELECT employee_id,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

#MySQL不支持 FULL 进行满外链接

#UNION：会执行去重操作
#UNION ALL：不会执行去重操作

#在实际开发当中，能用UNION ALL 就不用 UNION

#中图：内连接
#SQL99

SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

#SQL92

SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;

#左上图：左外连接

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;

#右上图：右外连接

SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;

#左中图
#左上图的基础上去掉内连接

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

#右中图
#右上图的基础上去掉内连接

SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

#左下图
#方式1：左上图 UNION ALL

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

#方式2：右上图 UNION ALL 左中图

SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
UNION ALL
SELECT employee_id,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 employee_id,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 employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;