吕佳苗 满外连接

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

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

-- 左下图：满外连接

-- 方式1：左上图UNION ALL右中图

SELECT employee_id,department_name FROM employees employees e LEFT JOIN departments d ON e.department_id=d.department_id UNION ALL

SELECT employee_id,department_name FROM employees employees e RIGHT JOIN departments d ON e.department_id=d.department_id WHERE e.employee_id IS NULL;

SELECT1+106+16;

-- 方式2：右上图UNION ALL左中图

SELECT employee_id,department_name FROM employees employees e RIGHT JOIN departments d ON e.department_id=d.department_id UNION ALL

SELECT employee_id,department_name FROM employees employees e LEFT JOIN departments d ON e.department_id=d.department_id WHERE e.employee_id IS NULL;

-- 右下图：左中图UNION ALL 右中图

SELECT employee_id,department_name FROM employees employees e LEFT JOIN departments d ON e.department_id=d.department_id IS NULL UNION ALL

SELECT employee_id,department_name FROM employees employees e RIGHT JOIN departments d ON e.department_id=d.department_id WHERE e.employee_id IS NULL;