MySQL 多表查询

也称为关联查询，指多个表一起完成查询的操作。需要将关联字段进行连接

错误方法

SELECT employee_id,department_name
FROM employees,departments;# 查询出2889条数据 107*27
1
2

原因：出现了笛卡尔积的错误，即两个表的每个数据都进行了一次匹配,类似于坐标

正确方法 添加连接条件

SELECT T1.employee_id,T2.department_name,T1.department_id
FROM employees T1,departments T2
WHERE T1.department_id = T2.department_id;
1
2
3

多表查询分类：

等值连接和非等值连接

非等值连接即数据的连接条件为一个连续区间而不是特定值

# 按不同工资等级获取员工信息并升序排列
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
ORDER BY grade_level ASC; 
1
2
3
4
5

自连接和非自连接

同一张表中的不同列存在关联，通过这些关联进行的连接是自连接

# 在同一张表中列出员工和对应管理者的名字及id
SELECT e.employee_id,e.last_name,m.employee_id manager_id,m.last_name
FROM employees e,employees m
WHERE e.manager_id = m.employee_id;
1
2
3
4

内连接和外连接

内连接(INNER JOIN)：两个表在连接过程中，结果中不包含不匹配的行（含null）

SQL语法中有SQL92 和 SQL99，SQL99语法可读性更好但是语句更“繁琐”

# 内连接 SQL92语法
SELECT e.last_name,m.last_name manager_name
FROM employees e,employees m
WHERE e.manager_id = m.employee_id;#仅显示106行，内连接（存在一个员工管理者为null）
1
2
3
4

# SQL99语法实现内连接
SELECT e.last_name,d.department_name,l.city
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id;
# 用到哪个表就join哪个，on加连接条件
1
2
3
4
5
6
7

外连接(OUTER JOIN)：两个表在连接过程中，不论匹配不匹配都会显示该行

左外连接(LEFT OUTER JOIN)
右外连接(RIGHT OUTER JOIN)
满外连接(FULL OUTER JOIN)

# 左外连接
SELECT last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;# 107行 空部门的员工
1
2
3
4

# 右外连接
SELECT last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;# 122行 空员工的部门
1
2
3
4

注意：SQL92不能实现外连接，SQL99可以实现内外连接，但是不能实现满外连接

SQL99 多表查询

基本语法： **JOIN ON ** 结构
JOIN+表，ON+连接条件

# 标准语言结构
SELECT table1.column, table2.column,table3.column
FROM table1
    JOIN table2 ON table1 和 table2 的连接条件
        JOIN table3 ON table2 和 table3 的连接条件
1
2
3
4
5

七种 SQL JOINS✨

UNION 和 UNION ALL

语法格式（即连接SELECT语句）：

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
1
2
3

UNION 并集，会执行去重操作，效率低
UNION ALL 并集，不会执行去重操作，效率高
一般使用后者UNION ALL

例子：

# 查询哪些部门没有员工
# 右外连接
SELECT d.department_id,d.department_name,e.employee_id
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;
# 左外连接
SELECT d.department_id,e.department_id,d.department_name,e.employee_id
FROM departments d LEFT JOIN employees e
ON e.department_id = d.department_id
WHERE e.department_id IS NULL

# 说明：e.department_id和e.employee_id都可作为最后的判断条件。
# 因为外连接后新增的16条数据的e.department_id和e.employee_id都为null
1
2
3
4
5
6
7
8
9
10
11
12
13
14

需注意：连接条件e.department_id = d.department_id并不是将两边合并，结果表中同时存在e.department_id 和 d.department_id，只是前者新增了16行空值(e.employee_id同样)