05.多表查询

多个表中查询数据

多表查询的错误示范

# 现有表 employees、departments表
# 查询last_name为Abel的人在哪个城市工作
SELECT employee_id,department_name FROM employees,departments;

# 这种查询方式的查询结果是笛卡尔积，查询结果数量是 表1数量*表2数量
# 等同于如下的交叉连接查询方式
SELECT employee_id,department_name FROM employees CROSS JOIN departments;

多表查询的正确方式(需要有连接条件)

# 带连接条件的多表查询方式
SELECT employee_id,department_name 
FROM employees,departments 
WHERE employees.department_id = departments.department_id;
# 如果查询语句中多个表中有相同字段，必须指明此字段所在的表
SELECT employees.employee_id,departments.department_name,employees.department_id
FROM employees,departments
WHERE employees.department_id = departments.department_id;
# 从sql优化的角度，建议多表查询，每个字段都指明其所在的表
 
# 可以给表起别名，在SELECT和WHERE中使用表的别名，而且一旦有了别名，在SELECT和WHERE中必须使用别名
# 这里注意了，虽然可以给字段起别名但是只能在ORDER BY中使用不能在WHERE中使用
SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.department_id = dept.department_id;
# 但是如果起了别名之后还是用表名就会报错
SELECT emp.employee_id,dept.department_name,emp.department_id
FROM employees emp,departments dept
WHERE emp.department_id = departments.department_id;
# 报错 > 1054 - Unknown column 'departments.department_id' in 'where clause'

# 如果要查询n个表中的数据，至少需要n-1个连接条件
SELECT emp.employee_id,dept.department_name,emp.department_id,city
FROM employees emp,departments dept,locations l
WHERE emp.department_id = dept.department_id
AND l.location_id = dept.location_id;

多表查询的分类

按照不同的角度可以分为三类

等值连接和非等值连接

等值连接和非等值就是连接条件的不同

# 上面的内容都是等值连接
# 下面介绍非等值连接
# 查询每个员工工资的等级
SELECT e.employee_id,e.salary,j.grade_level
FROM employees e,job_grades j
WHERE e.salary >= j.lowest_sal AND e.salary <= j.highest_sal

自连接和非自连接

# 自连接就是查询的数据在自己的表中
# 举个例子：一张员工表，查询每个员工的管理者，员工表中的管理者id与员工id对应
# 两张表都是employees，起不同的别名实现自连接查询
SELECT emp.employee_id,emp.first_name,emp_manager.employee_id manager_id,emp_manager.first_name manager_name
FROM employees emp, employees emp_manager
WHERE emp.manager_id = emp_manager.employee_id;

内连接和外连接

简单地说，内连接就是两张表的交集，上面介绍的都是内连接

外连接就是除了两张表的交集还要左表或者右表不匹配的行
外连接分为：左外连接、右外连接、满外连接

# 内连接 这里只能查询出106条数据，因为有一台数据的department_id为null
SELECT employee_id,department_name 
FROM employees,departments 
WHERE employees.department_id = departments.department_id;

# 外连接
# 查询所有员工的的department_id
# 左外连接(包含左表的全部数据)
SELECT employee_id,department_name 
FROM employees LEFT JOIN departments 
ON employees.department_id = departments.department_id;

# 右外连接(包含右表的全部数据)  可以省略RIGHT OUTER JOIN
SELECT employee_id,department_name 
FROM employees RIGHT JOIN departments 
ON employees.department_id = departments.department_id;

# 满外连接, MySQL不能通过FULL OUTER JOIN 实现满外连接
# MySQL需要借助UNION的使用 
# UNION 去重结果
# UNION 不去重结，单纯的把两张表加起来
SELECT employee_id,department_name 
FROM employees LEFT JOIN departments 
ON employees.department_id = departments.department_id
UNION
SELECT employee_id,department_name 
FROM employees RIGHT JOIN departments 
ON employees.department_id = departments.department_id;

7种JOIN的实现

SQL的JOIN有其中情况分别如下：

# 左外连接
SELECT employee_id,department_name 
FROM employees LEFT JOIN departments 
ON employees.department_id = departments.department_id;

# 左外连接去掉中间重合部分
SELECT employee_id,department_name 
FROM employees LEFT JOIN departments 
ON employees.department_id = departments.department_id
WHERE employees.department_id IS NULL;

# 内连接
SELECT employee_id,department_name 
FROM employees JOIN departments 
ON employees.department_id = departments.department_id;

# 右外连接
SELECT employee_id,department_name 
FROM employees RIGHT JOIN departments 
ON employees.department_id = departments.department_id;

# 右外连接去掉中间重合部分
SELECT employee_id,department_name 
FROM employees RIGHT JOIN departments 
ON employees.department_id = departments.department_id
WHERE employees.department_id IS NULL;

# 全外连接，有多种写法
# 左外连接+右外连接 只能用UNION不能用UNION ALL，因为有重复部分需要去重
SELECT employee_id,department_name 
FROM employees LEFT OUTER JOIN departments 
ON employees.department_id = departments.department_id
UNION
SELECT employee_id,department_name 
FROM employees RIGHT OUTER JOIN departments 
ON employees.department_id = departments.department_id;

# 左外连接+ （右外连接-重叠部分）可以用UNION或者UNION ALL
SELECT employee_id,department_name 
FROM employees LEFT OUTER JOIN departments 
ON employees.department_id = departments.department_id
UNION
SELECT employee_id,department_name 
FROM employees RIGHT JOIN departments 
ON employees.department_id = departments.department_id
WHERE employees.department_id IS NULL;

# （左外连接-重叠部分） + 右外连接 可以用UNION或者UNION ALL
SELECT employee_id,department_name 
FROM employees LEFT OUTER JOIN departments 
ON employees.department_id = departments.department_id
WHERE departments.department_id IS NULL
UNION
SELECT employee_id,department_name 
FROM employees RIGHT JOIN departments 
ON employees.department_id = departments.department_id;

# （左外连接-重叠部分） + （右外连接-重叠部分） + 重叠部分 可以用UNION或者UNION ALL
SELECT employee_id,department_name 
FROM employees LEFT OUTER JOIN departments 
ON employees.department_id = departments.department_id
WHERE departments.department_id IS NULL
UNION
SELECT employee_id,department_name 
FROM employees RIGHT JOIN departments 
ON employees.department_id = departments.department_id
WHERE employees.department_id IS NULL
UNION
SELECT employee_id,department_name 
FROM employees JOIN departments 
ON employees.department_id = departments.department_id;

SQL99语法特性

自然连接

自然连接可以不用指定字段相同，自动匹配相同的字段

# 原来的写法
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`;

# 使用自然连接的写法
SELECT employee_id,last_name,department_name
FROM employees NATURAL JOIN departments;

USING

USING ()# 原来的写法
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`;

# 使用自然连接的写法
SELECT employee_id,last_name,department_name
FROM employees JOIN departments
USING (department_id,manager_id)