NNN

#满外连接(mysql不支持使用FULL OUTER JOIN)
SELECT employees_id,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

#开发中能用union all 就不用union（需要去重 效率低）

#左右各一半的话 左中+右中

#中图
#99
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.department_id=d.department_id;

#92
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 e.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;

#满外连接 
#左上+右中
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 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 e.department_id IS NULL;

#左右各一半:左中+右中
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id= d.department_id
WHERE e.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;