一.多表连接的实现

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