06.连接查询

含义：连接查询又称为多表查询，当查询的字段来自多个表时，就会用到连接查询。

1. 笛卡尔乘积

笛卡尔乘积现象：表 1 有 m 行，表 2 有 n 行，则查询结果有 m*n 行。

SELECT `name`, boyName FROM boys, beauty;

发生原因：没有有效的连接条件 解决办法：添加有效的连接条件

SELECT `name`, boyName FROM boys, beauty
WHERE beauty.boyfriend_id=boys.id;

2. 连接查询分类

2.1 按年代分

1. sql192 标准【只支持内连接】
2. sql199 标准【推荐】支持内连接+外连接（左外和内外）+交叉连接

2.2 按功能分

3. 等值连接(sql 92 标准)

3.1 特点

1. 多表等值连接的结果为多表的交集部分
2. n 表连接，至少需要 n-1 个连接条件
3. 多表的顺序没有要求
4. 一般需要为表取别名
5. 连接可以搭配前面所有子句使用，比如排序、分组、筛选

3.2 一般使用

案例 1: 查询女神名和对应的男神名

USE girls;
SELECT `name`, boyName
FROM boys, beauty
WHERE beauty.boyfriend_id=boys.id;

案例 2：查询员工名和对应的部门名

USE myemployees;
SELECT last_name, department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;

3.3 为表取别名

优点：提高简洁度、区分重名字段 注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

案例 2：查询员工名、工种号、工种名

SELECT e.last_name,e.job_id,j.job_title
FROM jobs AS j， employees AS e
WHERE e.job_id=j.job_id;

3.4 两表顺序可以调换

SELECT e.last_name,e.job_id,j.job_title
FROM employees e,jobs j
WHERE e.job_id=j.job_id;

3.5 可以加筛选

案例 1: 查询有奖金的员工名、部门名

SELECT last_name,department_name,commission_pct
FROM employees e, departments d
WHERE e.department_id=d.department_id
AND e.commission_pct IS NOT NULL;

案例 2：查询城市名中第二个字符为 o 的部门名和城市名

SELECT department_name,city
FROM departments d, locations l
WHERE d.location_id=l.location_id
AND city LIKE '_o%';

3.6 可以加分组

案例 1：查询每个城市的部门个数

SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.location_id=l.location_id
GROUP BY city;

案例 2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

SELECT department_name, d.manager_id,MIN(salary)
FROM departments d, employees e
WHERE d.department_id=e.department_id
AND commission_pct IS NOT NULL
GROUP BY department_name,d.manager_id;

3.7 可以加排序

案例：查询每个工种的工种名和员工的个数，并且按员工个数降序

SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.job_id=j.job_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;

3.8 可以实现三表连接

案例：查询员工名、部门名和所在城市

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
AND city LIKE 's%'
ORDER BY department_name DESC;

4. 非等值连接(sql 92 标准)

案例 1：查询员工工资和工资级别

SELECT salary, grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal
AND g.grade_level='A';

5. sql99 语法

5.1 基本语法与分类

语法：

SELECT 查询列表
FROM `表1` 别名 [连接类型]
JOIN `表2` 别名
ON 连接条件
[WHERE 筛选条件]
[GROUP BY 分组]
[HAVING 筛选条件]
[ORDER BY 排序列表]

分类：

5.2 内连接

基本语法：

SELECT 查询列表
FROM 表1 别名
INNER JOIN 表2 别名
ON 连接条件

1. 等值连接

特点：

1. 可以添加排序、分组、筛选
2. inner 可以省略
3. 筛选条件放在 where 后，连接条件放在 on 后
4. 和 sql92 标准等值连接效果一样

案例 1：查询员工名、部门名

SELECT last_name, department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id;

案例 2： 查询名宇字中包含 e 的员工名和工种名（添加筛选）

SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.job_id=j.job_id
WHERE last_name LIKE '%e%';

3. 查询部门个数>3 的城市名和部门个数(添加分组和筛选）

SELECT city,COUNT(*)
FROM departments d
INNER JOIN locations l
ON d.location_id=l.location_id
GROUP BY city
HAVING COUNT(*)>3;

4. 查询哪个部门的部门员工个数>3 的部门名和员工个数，并按个数降序（添加排序）

SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.department_id=d.department_id
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;

5. 查询员工名、部门名、工种名，并按部门名降序（多表连接）

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;

2. 非等值连接

案例 1：查询员工的工资级别

SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

案例 2：查询每个工资级别的个数>20，并按级别排序

SELECT COUNT(*),grade_level
FROM employees e
JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;

3. 自连接

查询包含字符 k 的员工名字和上级名字

SELECT e.last_name,m.last_name
FROM employees e
INNER JOIN employees m
ON e.manager_id=m.employee_id
WHERE e.last_name LIKE '%k%';

5.3 外连接

应用场景：用于查询一个表中有，另一个表中没有的记录。 特点：

1. 外连接的查询结果为主表中的所有记录，如果从表中有和他匹配的，则显示匹配的值，如果从表中没有，则显示 null。 外连接查询结果 = 内连接结果 + 主表中有而从表中没有的记录。
2. 左外连接中：left 左边的为主表 右外连接：right 右边的为主表
3. 左外和右外交换两个表的顺序，可以实现同样的效果。
4. 全外连接 = 内连接 + 表 1 有但表 2 没有 + 表 2 有但表 1 没有的

案例 1：左、右外连接

-- 引入：查询男朋友不在男生表的女神名
SELECT b.`name`, bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id=bo.id
WHERE bo.id IS NULL;

案例 2：查询哪个部门没有员工

-- 左外连接
SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id IS NULL;
-- 右外连接
SELECT d.*,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.department_id=e.department_id
WHERE e.employee_id IS NULL;

案例 3：全外连接

-- 不支持！
USE girls;
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON b.boyfriend_id = bo.id;

5.4 交叉连接

SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;

5.5 集合表示