多表查询
# 多表查询
# 1、查看类型
DESC employees;
DESC departments;
DESC locations;
# 2. 出现笛卡尔积的错误
错误的原因：缺少了多表的连接条件
错误的实现方式：每个员工都与每个部门匹配了一遍。
SELECT employee_id,department_name
FROM employees,departments; #查询出 2889 条记录 #错误的方式
SELECT employee_id,department_name
FROM employees CROSS JOIN departments;#查询出 2889 条记录
- 多表查询的正确方式：需要有连接条件
SELECT employee_id,department_name
FROM employees,departments
WHERE employees.`department_id` = departments.department_id;
4.如果查询语句中出现了多个表中都存在的字段，则必须指明此字段所在的表。
SELECT employees.employee_id,departments.department_name,employees.department_id
FROM employees,departments
WHERE employees.`department_id` = departments.department_id; #建议：从 sql 优化的角度，建议多表查询时，每个字段前都指明其所在的表。
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` = departments.department_id;
6.结论：如果有 n 个表实现多表的查询，则需要至少 n-1 个连接条件
练习：查询员工的 employee_id,last_name,department_name,city
SELECT emp.employee_id,emp.last_name,dep.department_name,loca.city
FROM employees emp,departments dep,locations loca
WHERE emp.department_id=dep.department_id AND dep.location_id=loca.location_id;
# 3 多表查询的分类
# 等值连接 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`;
WHERE e.`salary` >= j.`lowest_sal` AND e.`salary` <= j.`highest_sal`;
# 自连接 vs 非自连接
SELECT \* FROM employees; #自连接的例子： #练习：查询员工 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`;
# 内连接 vs 外连接
内连接：合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
SELECT employee_id,department_name
FROM employees e,departments d
WHERE e.`department_id` = d.department_id; #只有 106 条记录
外连接：合并具有同一列的两个以上的表的行, 结果集中除了包含一个表与另一个表匹配的行之外，还查询到了左表 或 右表中不匹配的行。
外连接的分类：左外连接、右外连接、满外连接
左外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左表中不满足条件的行，这种连接称为左外连接
右外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回右表中不满足条件的行，这种连接称为右外连接
练习：查询所有的员工的 last_name,department_name 信息
SELECT last_name,department_name
FROM employees e INNER JOIN departments d
ON e.`department_id` = d.`department_id`;
练习：查询所有的员工的 last_name,department_name 信息
左外连接：
SELECT last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`;
#右外连接：
SELECT last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
#满外连接：mysql 不支持 FULL OUTER JOIN
SELECT last_name,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;
# 4 UNION 和 UNION ALL 的使用
UNION：会执行去重操作
UNION ALL:不会执行去重操作
结论：如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用 UNION ALL 语句，以提高数据查询的效率。
SELECT _ FROM departments
UNION
SELECT _ FROM locations;
# 5 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`
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;
# 6 SQL99 语法的新特性
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
# 7 自然连接
NATURAL JOIN : 它会帮你自动查询两张连接表中所有相同的字段，然后进行等值连接。
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;
USING
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);
# 多表查询的课后练习
# 1.显示所有员工的姓名，部门号和部门名称。左外连接
SELECT e.last_name,d.department_id,d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;
# 2.查询 90 号部门员工的 job_id 和 90 号部门的 location_id
SELECT job_id,location_id
FROM employees e INNER JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id=90;
# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT e.last_name,d.department_name,d.location_id,l.city
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id LEFT JOIN locations l
ON d.location_id = l.location_id
WHERE e.commission_pct IS NOT NULL;#35 条记录 #有奖金的人
SELECT \*
FROM employees
WHERE commission_pct IS NOT NULL; #35 条记录
# 4.选择 city 在 Toronto 工作的员工的 last_name , job_id , department_id , department_name
SELECT e.last_name , e.job_id , e.department_id , d.department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE l.`city` = 'Toronto';
# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
SELECT d.department_name,l.street_address,e.last_name,e.job_id,e.salary
FROM departments d LEFT JOIN employees e
ON e.`department_id` = d.`department_id`
LEFT JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE d.`department_name` = 'Executive';
# 6.查询部门名为 Sales 或 IT 的员工信息
SELECT \*
FROM employees e INNER JOIN departments d
ON e.department_id = d.department_id;