MYSQL- 使用SQL99 实现7种JOIN 操作

DESC employees ;

 DESC departments;

## 中图 就是内连接
SELECT last_name,department_name
FROM employees e JOIN departments d 
ON e.department_id=d.department_id

##sql 99 内连接
SELECT last_name,department_name,city 
FROM employees e INNER JOIN departments d 
ON e.department_id=d.department_id
JOIN locations l
on d.location_id=l.location_id

##sql 99 左上图左外连接
SELECT last_name,department_name
FROM employees e left outer join departments d 
ON e.department_id=d.department_id;

#右上图，右外连接
SELECT last_name,department_name
FROM employees e right OUTER JOIN departments d 
ON e.department_id=d.department_id;

#左中图
SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments d 
ON e.department_id=d.department_id
WHERE d.department_id IS NULL ;

#右中图
SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d 
ON e.department_id=d.department_id
WHERE e.department_id IS NULL ;

#左下图，满外连接 
# 方法一左上图+ 右中图
SELECT last_name,department_name
FROM employees e left outer join departments d 
ON e.department_id=d.department_id
UNION ALL
SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d 
ON e.department_id=d.department_id
WHERE e.department_id IS NULL ;

#左下图，满外连接 
# 方法二右上图+ 左中图
SELECT last_name,department_name
FROM employees e right OUTER JOIN departments d 
ON e.department_id=d.department_id
union ALL 
SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments d 
ON e.department_id=d.department_id
WHERE d.department_id IS NULL ;

#右下图 左中加右中
SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments d 
ON e.department_id=d.department_id
WHERE d.department_id IS NULL 
union all 
SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d 
ON e.department_id=d.department_id
WHERE e.department_id IS NULL ;