#04 多表查询
#常用表
DESC employees;

DESC departments;

DESC locations;

#查询员工名为'Abel'的人在哪个城市工作？
SELECT *
FROM employees
WHERE last_name = 'Abel';

SELECT *
FROM departments
WHERE department_id = 80;

SELECT *
FROM locations
WHERE location_id = 2500;

#1.多表查询的实现--关联查询

#错误实现,交叉连接(笛卡尔积)
SELECT employee_id,department_name
FROM employees,departments; #查询出2889条记录

#··需要在以上基础添加，多表的连接条件
SELECT employee_id,department_name
FROM employees,departments
WHERE employees.`department_id` = departments.`department_id`;

#··错误代码： 1052
#Column 'department_id' in field list is ambiguous(不明确的)
SELECT employee_id,department_name,department_id
FROM employees,departments
WHERE employees.`department_id` = departments.`department_id`;

#查询语句出现了多个表中都存在的字段，则必须指明此字段所在的表
SELECT employee_id,department_name,employees.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.`department_id`;

#建议：从sql优化的角度，建议多表查询时，每个字段前都要指明其所在的表

#**可以给表起别名,在SELECT和WHERE中使用表的别名
SELECT employee_id,department_name,emp.department_id,dept.`department_name`
FROM employees emp,departments dept
WHERE emp.`department_id` = dept.`department_id`;

#**一旦给表起了别名，一旦在SELECT和WHERE中使用表名的话，则必须使用表的别名，而不能再用表的原名。
#Unknown column 'employees.department_id' in 'where clause'
SELECT employee_id,department_name,emp.department_id,dept.`department_name`
FROM employees emp,departments dept
WHERE employees.`department_id` = departments.`department_id`;

#结论：如果有n个表实现多表的查询，则需要至少n-1个连接条件
#查询员工的employee_id,last_name,department_name,city
SELECT e.employee_id,e.last_name,d.department_name,l.city
FROM employees e,departments d,locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`

#2.多表查询的分类
/*
角度一：等值连接 vs 非等值连接

角度二：自连接 vs 非自连接

角度三：内连接 vs 外连接

*/

#2.1 等值连接 vs 非等值连接
#非等值连接 例子
SELECT e.last_name,e.salary,j.grade_level
FROM employees e,job_grades j
WHERE e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`

#2.2 自连接 vs 非自连接
#自连接 例子
#查询员工id及上司的id和姓名
SELECT e.`last_name`,e.`employee_id`,m.`employee_id`,m.`last_name`
FROM employees e,employees m
WHERE e.`manager_id` = m.employee_id;

#2.3 内连接与外连接

#内连接--合并具有同一列的两个以上的表的行，结果集中不包含一个表和另一个表不匹配的行
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`;

#外连接--合并具有同一列的两个以上的表的行，结果集中除了包含一个表和另一个表匹配的行
# 还查询到了左表或右表中不匹配的行

#外连接的分类：左外连接、右外连接、满外连接

#左(右)外连接：两个表在连接过程中除了返回满足连接条件的行以外，
# 还返回左(右)表中不满足条件的行，这种连接方式称为左(右)外连接

#查询所有员工的last_name和department_name信息

#SQL92语法实现外连接：使用+ 内连接实现如上，略
#但MySQL不支持SQL92外连接语法！ERROR
SELECT e.`last_name`,d.`department_name`
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`(+);

#SQL99语法如何实现多表查询， MySQL支持，7种join

#SQL99语法实现内连接：JOIN ON
SELECT last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

SELECT last_name,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语法实现外连接：LEFT (OUTER) JOIN ON

#左外连接,查询所有员工的last_name和department_name信息
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`;

#满外连接：MySQL不支持FULL OUTER JOIN ON方法

SELECT last_name,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;


#2.4 UNION 、UNION ALL 操作符

#UNION：会执行去重操作
#UINION ALL：不会执行去重操作
#结论：如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据
# 则尽量使用UNION ALL语句，以提高数据查询的效率

#7种JOIN的实现：

#1. 内连接：取相同部分
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

#2. 左外连接：相同部分+左表选中 左上图
SELECT employee_id,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

#3. 右外连接：相同部分+右表选中 右上图
SELECT employee_id,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

#4. 左外连接减去相同部分 左中图
SELECT employee_id,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL;

#5. 右外连接减去相同部分 右中图
SELECT employee_id,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

#6. 满外连接：右外连接+左外连接-一个重复部分

#方式一：左上图 UNION ALL 右中图
SELECT employee_id,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT OUTER 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 OUTER 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 OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;

#7. 右外连接+左外连接减去所有重复部分
#左中图 UNION ALL 右中图
SELECT employee_id,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 employee_id,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;