[打卡]内连接

#全部
SELECT employee_id,department_name
from employees e FULL 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;

#中间
SELECT employee_id,department_name
from employees e INNER JOIN departments d
on e.department_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
on e.department_id=d.department_id;

#满外连接 MySQL不支持full来进行满外连接
SELECT employee_id,department_name
from employees e FULL OUTER join departments d
on e.department_id=d.department_id;

#UNION 会执行去重操作
#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;

#右中图 右上图基础上去掉内连接
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 FULL OUTER JOIN departments d
on e.department_id=d.department_id;

#右下图
SELECT employee_id,department_name
from employees e FULL OUTER JOIN departments d
on e.department_id=d.department_id
where e.department_id is null
OR d.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 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;

#左中加右中
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
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;

#左中+中+右中
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
UNION ALL
SELECT employee_id,department_name
from employees e INNER 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;