[打卡]白铂溪 多表查询

#外连接的分类：左外、右外连接和满外连接

#满外连接

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;

SELECT 1+106+16;
#方式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;

#左中右中
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;