[打卡]sql——多表查询——满内连接

#msq不适用
SELECT employee_id,department_name
FROM employees e FULL OUTER 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;

#左上图：左外连接
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.employee_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.employee_id IS NULL;

#方式2
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.employee_id IS NULL;