[打卡]赵梓焓 _多表查询

-- 满外连接
#mysql不支持 FULL OUTER JOIN做满外查询

SELECT employee_id,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.employee_id = d.department_id;

#UNION做满外查询

#实际开发中，能用union all 就不用union

#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.department_id IS NULL;

#左上+右中
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 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 d.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
WHERE e.department_id IS NULL;