2024/4/19

SELECT employees_id,department_name
FROM employees e
#满外连接
SELECT EMPLOYEE_id,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

#MySQL 不支持FULL OUTER JOIN (但是ORACLE支持)所以以上代码不能执行
SELECT 1+106+16;、

#开发中能用UNION ALL就用UNION ALL效率比UNION高

#UNION 需要去重，效率低
#满外连接
#左上+右中

#左中+右上

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

#中图（即图中的内连接）
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.department_id = d.department_id;
#以上是SQL92语法
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
#以上为SQL99语法

#左上图：左外连接

SELECT employee_id,department_name
FROM employees e LEFT JOIN departmen;
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;

#满外

#左上+右中
SELECT employee_id,department_name
FROM employees e LEFT JOIN departmen;
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 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;

#左右各一半（即左中+右中）
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;