一.多表连接的实现
1.1 多表查询需要有连接条件
SELECT employee_id, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id
#其中两个表的department_id就是他们的连接条件
1.2 从sql优化的角度来说,建议多表查询时,每个字段前都指明其所在的表,可以给表起别名, 在SELECT和WHERE中使用别名, 并且使用别名后,在指定的时候不能再使用表原名
SELECT e.employee_id , d.department_name, d.department_id
FROM employees e, departments d
WHERE e.department_id = d.department_id
1.3 如果有n个表实现多表的查询,则需要至少n-1个连接条件
SELECT e.employee_id , 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
二.等值连接与非等值连接
上面就是等值连接,接下来我们来看非等值连接
job_grades表
grade_level | lowest_sal | highest_sal |
---|---|---|
A | 1000 | 2999 |
B | 3000 | 5999 |
C | 6000 | 9999 |
D | 10000 | 14999 |
E | 15000 | 24999 |
F | 25000 | 40000 |
需求: 要我们查询每个员工的工资水平等级
SELECT e.last_name, e.salary, jg.grade_level
FROM employees e, job_grades jg
#WHERE e.salary >= jg.lowest_sal AND e.salary <= jg.highest_sal
WHERE e.salary BETWEEN jg.lowest_sal AND jg.highest_sal
三.自连接与非自连接
上面为非自连接
自连接就是自己连接自己
#查询员工的顶头上司
SELECT e.employee_id, e.last_name, e2.manager_id, e2.last_name
FROM employees e, employees e2
WHERE e.manager_id = e2.employee_id
四.内连接与外连接
4.1:
上面都是内连接
内连接就是查询打印出满足条件的行
外连接: 分为左外连接, 右外连接, 满连接
左外连接就是除了打印满足条件行还把左表打印出来
右外连接同理
满外连接就是都打印出来
4.2:
在sql语言中有两套重要标准,分别为SQL92和SQL99标准
SQL92: 语法简单,但语句长,可读性差
SQL99: 语法较复杂, 但可读性强
4.3:
由于MySQL不支持92的外连接写法,所以我们直接来看SQL99的写法
1.内连接
JOIN ON
SELECT e.employee_id, d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
SELECT e.employee_id, 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
2.左外连接
LEFT JOIN ON
SELECT e.employee_id, d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
3.右外连接
RIGHT JOIN ON
SELECT e.employee_id, d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
4.满外连接
满外连接有UNION 和 UNION ALL
UNION: 返回两个查询的结果集的并集, 去除重复记录
UNION ALL: 返回两个查询的结果集的并集, 不去重
注意: 执行UNION ALL语句时所需要的资源比UNION语句少, 如果明确知道合并后的结果数据不存在重复数据, 或者不需要去除重复的数据,则尽量使用UNION ALL语句,以提高数据查询的效率
#就是把左外右外用UNION连接起来
SELECT e.employee_id, d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
UNION
SELECT e.employee_id, d.department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
五.sql99的新特性
自然连接 NATURAL JOIN
会自动查询两张连接表中相同的字段,进行等值连接
SELECT e.employee_id , e.last_name, d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
AND e.manager_id = d.manager_id
#相同
SELECT e.employee_id , e.last_name, d.department_name
FROM employees e NATURAL JOIN departments d
缺点: 会强制连接所有相同字段, 不够灵活
注意: 我们要控制连接表的数量,多表连接就相当于嵌套for一样,非常消耗资源,会让sql查询性能大大降低,因此不要连接不必要的表, 需要JOIN的字段,数据类型保持绝对一致