【MySQL】多表查询

# 第六章 多表查询

#1. 熟悉常见的几个表
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;

#3.多表查询如何实现
select employee_id, department_name    #2889条记录，显然不对
from employees, departments;  #每个员工都与每个部门都匹配了一遍，出现笛卡尔积错误
#错误原因，缺少了多表的连接条件

select *
from employees;     #107条记录

Select *
from departments;    #27条记录

#正确方式：需要有连接条件
select employee_id, department_name
from employees, departments
#两表连接条件
where employees.`department_id` = departments.`department_id`;

#4.若查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表
select employee_id, department_name, employees.department_id
from employees, departments
#两表连接条件
where employees.`department_id` = departments.`department_id`;

#建议：从sql优化的角度，建议多表查询时，每个字段前都指明其所在的表
select employees.employee_id, departments.department_name, employees.department_id
from employees, departments
#两表连接条件
where employees.`department_id` = departments.`department_id`;

#5.可以给表起别名，在select 和 where 中使用表的别名
select emp.employee_id, dept.department_name, emp.department_id
from employees emp, departments dept
where emp.`department_id` = dept.`department_id`;

#报错：如果一旦起了表的别名，在select或where中必须使用表的别名，而不能用表的原名
select emp.employee_id, departments.department_name, emp.department_id
from employees emp, departments dept
where emp.`department_id` = dept.`department_id`;

#6. 结论：如果有n个表实现多表的查询，则至少需要n-1个连接条件
#练习：查询员工的employee.id, last_name, department.name,city
select emp.employee_id, emp.last_name, dept.department_name, loc.city
from employees emp, departments dept, locations loc
where emp.department_id = dept.department_id and dept.location_id = loc.location_id;

#7. 多表查询的分类
/*
角度1：等值连接和非等值连接
角度2：自连接和非自连接
角度3：内连接和外连接
*/

#7.1 等值连接 VS 非等值连接
#非等值连接的例子：
select *
from job_grades;

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;

#7.2 自连接 VS 非自连接
#练习：查询员工id,员工姓名以及管理者id与姓名 [自连接]
select emp.employee_id, emp.last_name, mgr.employee_id, mgr.last_name
from employees emp, employees mgr
where emp.manager_id = mgr.employee_id;

#7.3 内连接 VS 外连接
#内连接：合并具有同一列的两个以上的表的行，结果集中不包括一个表与另一个表不匹配的行
select employee_id, department_name
from employees e, departments d
where e.`department_id` = d.`department_id`; #只有106条记录

外连接：合并具有同一列的两个以上的表的行，结果集中除了包括一个表与另一个表不匹配的行
#        还查询到了左表或右表中不匹配的行
#外连接的分离：左外连接、右外连接、满外连接
#练习：查询 所有的 员工的last_name, department_name信息 【所有 一定是外连接】
select employee_id, department_name
from employees e, departments d
where e.`department_id` = d.`department_id`;      #需要使用左外连接

#SQL92语法实现内连接：见上，略
#SQL92语法实现外连接：使用 +
select employee_id, department_name
from employees e, departments d
where e.`department_id` = d.department_id(+);      # 但是MySQL不支持这种写法

#SQL99语法中使用JOIN……ON的方式实现多表的查询，这种方式也能解决外连接的方式，MySQL支持
#SQL99语法如何实现多表查询

#SQL99语法实现 内连接：
select last_name, department_name
from employees e INNER JOIN departments d  #INNER可省略
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语法实现 外连接：
#查询 所有的 员工的last_name, department_name信息
select last_name, department_name
from employees e left outer JOIN departments d      #左外连接 outer可省略
ON e.department_id = d.department_id;

select last_name, department_name
from employees e right outer JOIN departments d      #右外连接 outer可省略
ON e.department_id = d.department_id;

#满外连接 
select last_name, department_name
from employees e full outer JOIN departments d    #很遗憾！ MySQL不支持 full outer JOIN
ON e.department_id = d.department_id;

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

#9. 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 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;

#左下图：满外连接
#方式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;
#方式2：左中图 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 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;

#10. SQL99语法新特性
#10.1 自然连接
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;
#可以把 自然连接 理解为SQL92中的等值连接。它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接
SELECT employee_id,last_name,department_name
FROM employees e natural JOIN departments d;

#10.2 USING的使用
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;
#支持使用 USING 指定数据表里的同名字段进行等值连接
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);