[打卡]多表查询_03

/* 
内连接 vs 外连接
咱们之前写的都是内连接
找一个之前写过的例子
*/
#查询员工id和部门名字
SELECT e.employee_id,d.department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id;
#一共106条记录，employees一共107条，少一条，是因为有一个department_id为空
#我们只是把左表和右表满足连接条件的数据给显示出来，其他的数据都没有要，把这种查询叫做内连接
#内连接:合并具有同一列的两个以上的表的行,结果集中不包含一个表与另一个表不匹配的行
SELECT * FROM employees;
#可能有员工没有部门，也可能有部门，但是这个部门还没有员工
#如果想把所有的都查出来，就叫做 外连接
#外连接：合并具有同一列的两个以上的表的行,结果集中除了包含一个表与另一个表匹配的行之外，还查询到了左表或右表中不匹配的行
#外连接的分类：左外连接、右外连接、满外连接
#左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，这种连接称为左外连接。
#右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行，这种连接称为右外连接。
#查询所有的员工id和部门名字
SELECT e.employee_id,d.department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id; #需要使用左外连接
#上述是不可行的
#外连接去做的话，涉及到两个语法SQL92和SQL99
#SQL92语法实现外连接：
#SQL92如何实现内连接：见上
#SQL92语法实现外连接：使用 +  -----MySQL不支持SQL92语法中使用外连接的写法！
SELECT e.employee_id,d.department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id(+);
#SQL99语法中使用JOIN...ON的方式实现多表的查询，这种方式也能解决外连接的问题，MYSQL是支持此种方式的
#SQL99语法如何实现多表的查询
#从长计议
#SQL99语法实现内连接
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
#也可以在JOIN前加个INNER，可省略
SELECT employee_id,department_name
FROM employees e INNER JOIN departments d
ON e.department_id = d.department_id;
# 加上city这个字段
SELECT employee_id,department_name,city
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id;
#SQL99语法实现外连接
#查询所有的员工id和部门名字
#左外连接
SELECT employee_id,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;
#OUTER 可以省略掉
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;
 
# 1 106 16
#新的需求 想有123 满外连接
SELECT 122-106
#满外连接 
SELECT employee_id,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;
#原来如此简单，挂了
# MySQL不支持FULL OUTER JOIN
-- ---------------------------------------------------------------------------
# UNION的使用
#来看看pdf
# 1+106+16
# 1+106+106+16
# UNION和UNION ALL的使用
# UNION：会执行去重操作
# UNION ALL:不会执行去重操作
# 开发当中，能用UNION ALL 就不用UNION
# UNION需要去重，效率低
# 左上和右中连在一起
# 右上和左中连在一起
# 左中和右中连在一起是下2
# 7种JOIN的实现
# 中图：内连接
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
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;
#107
#右上图：右外连接
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;
# 左下图：满外连接
# 方式1：左上图 UNION ALL 右中图
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;
# 方式1：左中图 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
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT 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 
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;