满外

#满外连接
SELECT employee_id,department_id
FROM employees e FULL JOIN departments d
ON e.department_id=d.department_id;
#MYSQL不支持FULL来进行满外连接

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

#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;

#左上图：左外连接(只能用99)
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;