[mysql] 连接查询

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

笛卡尔乘积现象

select count(*) from beauty; # 12
select count(*) from boys;   # 4

select count(*) from beauty,boys; # 48

## 没有指定连接条件，两者会默认规则匹配，最终的结果是两者的乘积
# 发生原因： 没有有效的连接条件
# 如何避免： 添加有效的连接条件

## 添加条件
SELECT beauty.name,
       boys.boyName
FROM beauty,boys
WHERE beauty.boyfriend_id = boys.id;

分类

按年代分类

SQL92

仅仅支持内连接.

SQL99【推荐】

1. 内连接
2. 外连接（左外和右外)
3. 交叉连接

按功能分类

1. 内连接
   1. 等值连接
   2. 非等值连接
   3. 自连接
2. 外连接
   1. 左外连接
   2. 右外连接
   3. 全外连接
3. 交叉连接

SQL92语法（仅内连接）

1. 等值连接

# 案例1 查询女神名和对应的男神名
USE girls;
SELECT beauty.name, boys.boyName
FROM boys, beauty
WHERE beauty.boyfriend_id = boys.id;

# 案例2 查询员工名和对应的部门名
USE myemployees;
SELECT employees.last_name, departments.department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

可以为表其别名

提高语句的简洁度，区分多个重名的字段。

如果起了别名，则不能使用原来的名字。

# 员工名 工种号 工种名
SELECT e.last_name,
       j.job_id,
       j.job_title
FROM employees AS e, jobs AS j
WHERE e.job_id = j.job_id;

添加筛选条件

# 查询有奖金的员工名、部门名
SELECT e.last_name,
       d.department_name
FROM employees AS e,
     departments AS d
WHERE e.department_id = d.department_id
AND e.commission_pct IS NOT NULL;

## 只需要添加AND

添加分组

# 查询每个城市的部门个数
SELECT l.`city`, COUNT(*) AS department_count
FROM departments AS d, locations AS l
WHERE d.location_id = l.location_id
GROUP BY city;

# 查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT d.department_name,
       d.manager_id,
       MIN(e.salary)
FROM employees AS e, departments AS d
WHERE e.department_id = d.department_id
AND e.commission_pct IS NOT NULL
GROUP BY e.department_id
## 无意义查询

# 查询每个工种的工种名和员工的个数 并按员工个数排序
SELECT j.job_title,
       COUNT(*) AS employees_count
FROM employees AS e, jobs AS j
WHERE e.job_id = j.job_id
GROUP BY j.job_id
ORDER BY employees_count DESC;

三表查询

# 三表
# 查询员工名、部门名和所在的城市
SELECT e.last_name,
       d.department_name,
       l.city
FROM employees AS e,
     departments AS d,
     locations AS l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;

总结

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

2. 非等值连接

非等号。

3. 自连接

将自己表作为多个表使用，使用别名区分。

# 查找员工名和上级的名称
SELECT e1.employee_id,
       e1.last_name,
       e2.employee_id,
       e2.last_name
FROM employees AS e1, employees AS e2
WHERE e2.employee_id = e1.manager_id;

SQL99语法

1. 内连接: inner
2. 外连接
   1. 左外: left [outer]
   2. 右外: right [outer]
   3. 全外: full [outer] 不支持
3. 交叉连接: cross

语法

select 查询列表
from 表1 别名 [连接类型]
join 表2 别名
on 连接条件
[where 筛选条件]
[group by 分组]
[having 筛选类型]
[order by 排序列表]

1. 内连接

语法：

select 列表
from 表 别名
inner join 表2 别名
on 连接条件;

1. 等值连接
2. 非等值连接
3. 自连接

1. 等值

# 1. 查询员工名、部门名
SELECT e.last_name,
       d.department_name
FROM employees AS e
INNER JOIN departments AS d
ON e.department_id = d.department_id;

# 效果与92一样，语义性强

# 2. 查询名字中包含e的员工名和工种名(添加筛选
SELECT e.last_name,
       j.job_title
FROM employees AS e
INNER JOIN jobs AS j
ON e.job_id = j.job_id
WHERE e.last_name LIKE '%e%';

# 3. 查询部门个数>3的城市名和部门个数（添加分组+筛选
SELECT l.city,
       COUNT(*) AS department_count
FROM departments AS d
INNER JOIN locations AS l
ON d.location_id = l.location_id
GROUP BY l.city
HAVING COUNT(*) > 3;

# 4. 查询哪个部门的部门员工个数>3的部门名和员工个数，并按个数降序(排序
SELECT d.department_name,
       COUNT(*) AS employee_count
FROM employees AS e
INNER JOIN departments AS d
ON e.department_id = d.department_id
GROUP BY d.department_id
HAVING COUNT(*) > 3
ORDER BY employee_count DESC ;

# 5. 查询员工名、部门、工种名、并按部门名降序(三表连接
SELECT e.last_name,
       d.department_name,
       j.job_title
FROM employees AS e
INNER JOIN departments AS d
ON e.department_id = d.department_id
INNER JOIN jobs AS j
ON e.job_id = j.job_id
ORDER BY d.department_name DESC ;

## 三表拥有顺序

特点:

1. 可以添加分组、排序、筛选
2. inner可以省略
3. 筛选条件放在where后面，连接条件放on后面，提高分离性，便于阅读
4. 等值连接和sql92效果一致

2. 非等值

# 查询员工的工资级别
SELECT e.last_name,
       e.salary,
       JG.grade_level
FROM employees AS e
INNER JOIN job_grades AS JG
ON e.salary BETWEEN JG.lowest_sal AND JG.highest_sal;

# 查询工资级别的个数>2的个数，并且按工资级别降序
SELECT JG.grade_level,
       COUNT(*) AS grade_count
FROM employees AS e
INNER JOIN job_grades AS JG
ON e.salary BETWEEN JG.lowest_sal AND JG.highest_sal
GROUP BY JG.grade_level
HAVING COUNT(*) > 20
ORDER BY JG.grade_level DESC ;

3. 自连接

# 查询员工的名字、上级的名字
SELECT e.last_name,
       m.last_name
FROM employees AS e
INNER JOIN employees AS m
ON e.manager_id = m.employee_id;

2. 外连接

用于查询一个表中有，另一个表中没有的记录。

特点：

1. 外连接的查询结果为主表中的所有记录

   1. 如果从表中有和它匹配的，则显示匹配的值
   2. 如果从表中没有和它匹配的，则显示null
   3. 外连接的查询结果=内连接的结果+主表中有而从表中没有的记录

2. 左外连接，left左边的是主表

   右外连接，right右边的是主表

3. 左外和右外交换两个表的顺序，可以实现同样的效果

4. 全外连接=内连接结果+表1中有但表2中没有+表2中有但表1中没有的

# 查询男朋友 不在男神表的女神名
USE girls;
SELECT beauty.id,
       beauty.name
FROM beauty
LEFT OUTER JOIN boys
ON beauty.boyfriend_id = boys.id
WHERE boys.id IS NULL;
## boys.id 是主键，所以必不为空，可以判断

# 查询哪个部门没有员工
USE myemployees;
SELECT d.department_name
FROM departments AS d
LEFT OUTER JOIN employees AS e
ON d.department_id = e.department_id
WHERE e.department_id IS NULL;

全外(full [outer])：不支持

3. 交叉连接

99语法实现的笛卡尔乘积。

对比SQL92 和 SQL99

SQL99 语义性更强，支持连接方法更多。