Aloe0417 --多表查询04

-- 3.满外连接
# MYSQL不支持 FULL 来进行满外连接
-- 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 e.employee_id,d.department_name
FROM employees e,departments d
WHERE 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;

-- 右外连接
SELECT e.employee_id,d.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 右表无内连接(即是两个查询用
#UNION ALL连接)
SELECT e.employee_id,d.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 e.department_id IS NULL;
#2.右外连接 UNION ALL 左表无内连接(即是两个查询用
#UNION ALL连接)
SELECT e.employee_id,d.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;