SQL语句（四）联表查询
一、关联查询的分类
按年代分
sql92:仅仅支持内连接
sql99【推荐】:支持内连接+外连接（左外，右外）+交叉连接
按功能分
- 内连接
- 等值连接
- 非等值连接
- 自连接
- 外连接
- 左外连接
- 右外连接
- 全外连接
- 交叉连接
二、sql92语法的连接
语法
SELECT 查询列表
FROM 待链接的多个表
WHERE 连接条件 [和筛选条件]
这里的连接条件写字段相等关系，如e.department_id = d.id等
1. 简单应用
查询员工名和对应的部门名
SELECT last_name 员工名,department_name 部门名
FROM employees, departments
WHERE employees.department_id=departments.department_id;
# 连接条件为employees.department_id=departments.department_id
2. 为表起别名
SELECT 查询列表
FROM 表名 AS 别名, ...
WHERE 连接条件等
查询员工名，工种号，工种名
SELECT last_name,e.job_id,job_title
FROM employees AS e, jobs AS j
WHERE e.job_id = j.job_id;
由于两个表中都有job_id这个字段，所以在SELECT中需要指明是哪张表
3. 加入筛选
查询有奖金的 员工名，部门名
SELECT e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.commission_pct IS NOT NULL; <--加入的筛选条件
查询位于的城市的城市名中第二个字符为o的部门名和城市名
SELECT city,department_name
FROM locations l,departments d
WHERE d.location_id=l.location_id
AND city LIKE '_o%';
4. 加入分组
查询每个城市的部门个数
SELECT city,COUNT(*) 部门个数
FROM locations l,departments d
WHERE l.location_id=d.location_id
GROUP BY city
查询每个工种的工种名和员工个数，并按员工个数降序
SELECT job_title,COUNT(*) 员工个数
FROM jobs j,employees e
WHERE j.job_id=e.job_id
GROUP BY job_title
ORDER BY 员工个数 DESC;
5. 三表连接
和两表连接是基本相同的，在WHERE语句中加入一个连接条件即可
获取所有员工的员工名，部门名和所在城市
SELECT last_name, department_name, city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;
6. 非等值连接
查询员工的工资和工资级别
job_grades表：
SELECT salary, grade_level
FROM employees e, job_grades j
WHERE e.salary >= j.lowest_sal AND e.salary <= j.highest_sal;
7. 自连接
查询员工名及其对应上级的名称
SELECT e.last_name 员工,m.last_name 上级
FROM employees e, employees m
WHERE e.manager_id=m.employee_id;
三、sql99语法的连接
SELECT 查询列表
FROM 表1别名
【连接类型】 join 表2 别名
on 连接条件
where xxx
连接类型分类
- 内连接：
inner
和sql92的等值连接是等效的 - 外连接：
- 左外：
left [outer] - 右外：
right [outer] - 全外：
full [outer]
- 左外：
- 交叉连接：
cross
1. 内连接（INNER JOIN）
获取所有的员工名和其对应的部门名
SELECT e.last_name, d.department_name
FROM employees e INNER JOIN departments d
ON e.department_id = d.department_id;
查询部门个数>3的城市名和部门个数
SELECT city, COUNT(1) 部门个数
FROM departments d INNER JOIN locations l
ON d.location_id = l.location_id
GROUP BY city
HAVING 部门个数 > 3;
查询员工名、部门名、工种名，并按部门名排序【三表连接】
SELECT last_name, department_name, job_title
FROM employees e INNER JOIN departments d
ON e.department_id = d.department_id
INNER JOIN jobs j
ON e.job_id = j.job_id
ORDER BY department_name DESC;
查询员工的工资级别【非等值连接】
SELECT last_name, salary, grade_level
FROM employees e INNER JOIN job_grades j
ON e.salary BETWEEN j.lowest_sal AND j.highest_sal
ORDER BY salary;
查询员工名即其对应的上级名【自连接】
SELECT e.last_name 员工名, m.last_name 上级名
FROM employees e INNER JOIN employees m
ON e.manager_id = m.employee_id;
由上面的例子可以看出，使用sql99的内连接(INNER JOIN)即可实现sql92的所有连接操作了。
2. 外连接
作用：查询一个表有，另一个表没有的记录
先使用girls.sql生成对应的数据，对应的文件可以在https://www.bilibili.com/video/BV12b411K7Zu?from=search&seid=2415880702283399133 这个b站视频的评论区中找到。
此时我们可以获得如下几张表：
然后我们可以分别使用内连接和外连接分别连接beauty和boys这两张表，查看结果的差异：
内连接：
SELECT *
FROM beauty b INNER JOIN boys y
ON b.boyfriend_id = y.id;
外连接（左外）：
SELECT *
FROM beauty b LEFT JOIN boys y
ON b.boyfriend_id = y.id;
可以发现，当左表（即beauty表）的boyfriend_id字段找不到boys表中对应的id进行连接时，它也仍会保留这一记录，而右表（即boys表）的记录则全部设置为Null。
一句话来说，就是左表的数据不管是否满足连接条件，都至少会保留在最终查询集的一条记录之中。
查找男朋友不在男神表的女神名【左外连接】：
SELECT `name`, boyName
FROM beauty b LEFT JOIN boys y
ON b.boyfriend_id = y.id
WHERE y.id IS NULL;
【右外连接】
SELECT `name`, boyName
FROM boys y RIGHT JOIN beauty b
ON b.boyfriend_id=y.id
WHERE y.id IS NULL;
查询没有联系女神的男生
SELECT `name`, boyName
FROM beauty b RIGHT JOIN boys y
ON b.boyfriend_id=y.id
WHERE b.id IS NULL;
查询哪个部门 没有员工
SELECT department_name, COUNT(*)
FROM departments d LEFT JOIN employees e
ON d.department_id = e.department_id
GROUP BY department_name
HAVING COUNT(*)=0;
3. 交叉连接
SELECT b.`name`, y.boyName
FROM beauty b CROSS JOIN boys y;
即返回笛卡尔积（即所有组合的可能），左表数据为m条，右表数据为n条，最终查询集数据为m * n条
