[打卡]张景凯 内连接、外连接、满外连接

#外连接的分类：左外连接、有外连接、满外链接

#满外连接
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;

#左中图
#左上图的基础上去掉内连接
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;


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