[打卡]MySQL---

-- 满外连接
-- MySQL不支持FULL 来进行满外连接
-- UNION:会执行去重操作
-- UNION ALL:不会执行去重操作
-- 在实际的开发中，能用UNION ALL 不用 UNION

SELECT*FROM employees;

SELECT employee_id,department_name
FROM employees e join departments d
on e.department_id=d.department

SELECT e.employee_id,d.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 e.employee_id,d.department_name
FROM employees e LEFT JOIN departments d
on e.department_id=d.department_id
WHERE d.department_id is null;

-- 右表无内连接
SELECT e.employee_id,d.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 e.employee_id,d.department_name
FROM employees e right JOIN departments d
on e.department_id=d.department_id
WHERE d.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 e.employee_id,d.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 e.employee_id,d.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 e.employee_id,d.department_name
FROM employees e right JOIN departments d
on e.department_id=d.department_id
WHERE e.department_id is null;