DQL语言6：连接查询

含义

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

 笛卡尔乘积现象：
	表1 m 行，表2 n 行， 结果=m*n 行

发生原因：没有有效的连接条件

连接条件的分类

按年代分类：

sql92标准： 仅仅支持内连接
sql99标准【推荐】：支持内连接+外连接（左外、右外）+交叉连接

按功能分类：

内连接： 等值连接， 非等值连接， 自连接
外连接： 左外连接， 右外连接， 全外连接
交叉连接

sql92标准

等值连接

总结：
① 多表等值连接的结果为多表的交集部分
② n表连接，至少需要(n-1)个连接条件
③ 多表的顺序没有要求
④ 一般需要为表取别名
⑤ 可以搭配排序、筛选、分组使用

1. 基本使用

-- 案例1：查询女神名和对应的男朋友
SELECT `name`, boyName FROM beauty, boys
WHERE beauty.`boyfriend_id`=boys.`id`;

-- 案列2：查询员工名和对应的部门名
SELECT last_name, department_name FROM employees, departments
WHERE employees.`department_id` = departments.`department_id`;

2. 为表起别名

作用：
①提高语句的简洁度
②区分多个重名的字段
注意： 先执行 from, 故如果为表起了别名，则不能再使用原来的表名

-- 查询员工名、工种号、工种名
SELECT last_name, j.job_id, job_title 
FROM employees AS e, jobs AS j
WHERE e.`job_id`=j.`job_id`;

3. 加筛选条件

-- 案例1：查询有奖金的员工名和部门名
SELECT last_name, department_name 
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%';

4. 加分组

-- 案例1： 查询每个城市的部门个数
SELECT city, COUNT(*) FROM locations l,departments d
WHERE d.`location_id`=l.`location_id`
GROUP BY city;

5. 加排序

-- 案例：查询每个工种的工种名和员工的个数，并按员工个数降序
SELECT job_title, COUNT(*) FROM jobs j, employees e
WHERE j.`job_id`=e.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;

6. 实现三表连接

-- 案例：查询员工名、部门名和所在的城市
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`;

非等值连接

-- 案例1： 查询员工的工资和工资级别
SELECT salary, grade_level FROM employees e, job_grades jg
WHERE salary BETWEEN jg.`lowest_sal` AND jg.`highest_sal`;

自连接

-- 案例： 查询员工名和上级的名称
SELECT e1.last_name 员工名, e2.last_name 上级名称
FROM employees e1, employees e2
WHERE e1.`manager_id`=e2.`employee_id`;

sql99标准

语法

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

**连接类型：**
内连接：inner 

外连接
	左外：left [outer]
	右外: right [outer]
	全外: full [outer]
	
交叉连接: cross

内连接

语法

	select 查询列表
	from 表1 别名 
	inner join 表2 别名 
	on 连接条件

分类

• 等值连接
• 非等值连接
• 自连接

特点

① 可添加排序、分组、筛选
② inner可省略
③ 筛选条件放在where后，连接条件放在on后，提高分离性，便于阅读

案例分析

1. 等值连接

# 1. 等值连接
-- 案例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 locations l
INNER JOIN departments d
ON l.`location_id`=d.`location_id`
GROUP BY city
HAVING COUNT(*)>3;

-- 案例4： 查询员工个数>3的部门名和员工个数，并按个数降序（添加排序）
SELECT department_name, COUNT(*) 
FROM departments d
INNER JOIN employees e
ON d.`department_id`=e.`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 j.`job_id`=e.`job_id`
ORDER BY department_name DESC;

2. 非等值连接

# 2. 非等值连接
-- 查询员工的工资级别
SELECT last_name, grade_level 
FROM employees e
INNER JOIN job_grades jg ON e.`salary` BETWEEN jg.`lowest_sal` AND jg.`highest_sal`;

3. 自连接

# 3. 自连接
-- 查询员工的名字、上级的名字
SELECT e1.`last_name` 员工名, e2.`last_name` 上级名
FROM employees e1
INNER JOIN employees e2
ON e1.`manager_id`=e2.`employee_id`;

外连接

应用场景

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

特点

1. 外连接的查询结果为主表中的所有记录
   如果 从表 中有和它匹配的，则显示匹配的值
   如果 从表 中没有和它匹配的，则显示null

   外连接的结果=内连接的结果+主表中有而从表中没有的记录

2. 左外连接中，left join左边的是主表
   右外连接中，right join右边的是主表

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

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

案例分析

1.左外和右外

-- 案例：查询没有男朋友的女神名
#左外
SELECT *
FROM beauty be LEFT JOIN boys bo ON be.`boyfriend_id`=bo.`id`
WHERE bo.id IS NULL;

#右外
SELECT be.`name`, bo.*
FROM boys bo RIGHT JOIN beauty be ON bo.`id`=be.`boyfriend_id`
WHERE bo.`id` IS  NULL;

2.全外连接

#全外连接（MySQL不支持）
注意：MySQL是不支持全外的连接的

交叉连接(实现笛卡尔乘积）

USE girls;
SELECT be.*, bo.*
FROM beauty be
CROSS JOIN boys bo;

sql92和sql99对比

• 功能：sql99支持的较多
• 可读性：sql99实现连接条件和筛选条件的分离，可读性较高