[打卡]李卜一 多表查询

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;

-- mySQL 不支持Full OUTER JOIN 做满外查询
SELECT employee_id,department_name
from employees e full outer
join departments d
on e.employee_id = d.department_id;

-- 左上+右中
-- 右上+左中
-- 左中+右中

-- 中图：内连接
-- 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
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;