[打卡]cyd

-- 满外查询
-- 错误写法(MySQL不支持FULL OUTER JOIN 做满外查询 )
SELECT employee_id,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

-- 在实际应用开发中，能用union all 就不用union，union需要去重，效率度
-- 满外连接
-- 左上 + 右中（123）
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 d.department_id IS NULL;

-- 左右各一半：左中 + 右中


-- 中图：内连接（16）
-- 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;

-- 左上图：(查询所有员工)(左外连接)（107）
SELECT employee_id,department_name
from employees e LEFT JOIN departments d
ON e.department_id =d.department_id;

-- 右上图：(查询有部门没员工)（右外连接）（122）
SELECT employee_id,department_name
from employees e right JOIN departments d
ON e.department_id =d.department_id;

-- 左中图：（有员工没有部门）(1)
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;

-- 右中图：（有部门没员工）（16）
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;

-- 左右各一半（把中间的去了）
-- 左中 + 右中(17=1+16)
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;