7. 连接查询 - 《mysql》

SQL语法版本
连接分类

实际开发中，数据是存储在多张表中的，我们需要将多张表联合起来，查询出数据，就叫连接查询或者叫跨表查询

SQL语法版本

SQL 92 语法 (1992 年出现)

例如：

select p.name, j.name from person p, job p where p.id = j.id;
select 表1字段， 表2字段 from 表1， 表2 where 条件1 and 条件2;

SQL 99 语法（1999年出现）

优点：将表之间的连接拎出来，结构清晰，如果对结果不满意，再到后面追加 where 条件过滤

select p.name, p.jobId , j.job_name, j.id from person p join job_post j on p.jobId = j.id;

// 上面一种写法， 是 join 前面省略了 inner
select p.name, p.jobId , j.job_name, j.id from person p inner join job_post j on p.jobId = j.id;

select 表1字段， 表2字段 from 表1 join 表2 on 条件1 where 条件2;

连接分类

1. 内连接

内连接中，有一个 inner 关键字， inner join ，其中 inner 可以省略，上面的 SQL语法版本有示例

1.1 等值连接

问题1： 多张表连接查询，若没有任何条件限制，会发生什么？

小知识点：

在进行多表连接查询的时候，尽量给表起一个别名，可读性高，效率高；有可能 A表有name字段， B表也有name字段，此时，查询的时候，如果不给别名，就不知道用哪个值了

select a.name, b.name from A a, B b;

// 没有加限制条件
select p.name, p.jobId, j.id, j.job_name from person p, job_post j;

person - 人员表有 16条记录
job_post - 职位表有14条记录

正常来说，一个人，对应职位表里面一个职位，那么查询出来的还是 16条记录，但是，我们执行上面的SQL以后，会发现，有 224条记录，怎么来的 16 *14 =224

直接将左边的每一条记录和右边每一条记录都匹配一次，我们发现，上面左边的人员表和右边的职位表，根本匹配不上

结论：
若两张表进行连接查询的时候，不加条件限制，则查询出来的结果是两张表记录的条数的乘积，这种现象称为 “笛卡尔积”，为了避免这种情况，应在两张表进行连接查询的时候，添加限制条件

还是上面的案例，加入限制条件
以下语法属于： SQL 92 语法内连接的等值连接

select p.name, p.jobId, j.id, j.job_name from person p, job_post j where p.jobId = j.id;

SQL 99 语法内连接的等值连接

select p.name , p.jobId, j.id, j.job_name from person p join job_post j on p.jobId = j.id;

注意：现在虽然查询的记录已经符合要求， 16条，但是查询的次数并没有变少，仍旧是 2张表的乘积 = 56，只是显示出来的结果都事有效记录，16条

1.2 非等值连接

案例2： 找出每一个员工对应的工资等级，要求显示员工名，工资，工资等级
分析：
员工表 — person 有 name 和salary
薪资等级表 — salgrade 有等级 grade

员工表-图-1

薪资等级表-图-2

薪资等级是一个范围类似 [1000, 3000] 那么就用到了 between xxx on xxxx
员工表中的 salary 在区间范围内，对应一个等级 salgrade

// SQL 99语法  使用 between and 设置区间
select p.name, p.salary, s.grade, s.losal as '最低薪资', s.hisal as '最高薪资' from person p join salgrade s on p.salary between s.losal and s.hisal;

// 使用 >= <=写法
select p.name, p.salary, s.grade, s.losal as '最低薪资', s.hisal as '最高薪资' from person p join salgrade s on p.salary >= s.losal and p.salary<= s.hisal;



// SQL 92语法 使用 where 加限定条件
select p.name, p.salary, s.grade, s.losal as '最低薪资', s.hisal as '最高薪资' from person p, salgrade s where p.salary between s.losal and s.hisal;

1.3 自连接

案例3： 找出每个员工的上级领导，要求显示员工名和对应的领导名

之前的案例，没有添加 leaderid，下面的表，新加的属性，重新造的数据，与前面的示例无关了

员工表-图

// SQL 99语法
select p.name '员工名称' , l.name as '领导名' from person p inner join person l on p.leaderid = l.id; 

// SQL 92语法
select p.name '员工名称' , l.name as '领导名' from person p, person l where p.leaderid = l.id;

上面有15条记录，但是这个查询出来只有 14条，注意，李明是没有 leaderid 的所以，是不会被匹配，这个李明应该是最大的领导

2. 外连接

2.1 左外连接

很简单的理解， A 和B两张表，左外连接，就是 join 以join左边的表为基础，右边的表映射过来，左边的表数据可以完全展示，右边的表不一定

select p.name, d.deptname as '部门名称' from  dept d left join person p on p.depId = d.id;

上面的 left join 表示以 join 左边的表为基础将右边的表的数据都映射过来（关联过来），左边表 dept 部门表，完全显示，右边的表人员表 Person 不一定完全显示

2.2 右外连接-右连接

右外连接和右连接是一回事，不同的叫法

示例4：** 找出每个员工对应的部门名称，要求部门名称全部显示

// 这个语句，只是将 员工对应的部门全部都显示，但是部门不止这些
select p.name, d.deptname as '部门名称' from person p join dept d on p.depId = d.id;

查看一下部门表

select * from dept;

实施部和工程部，对于示例4** **的要求来说，并没有达到，也就是说部门名称还没有完全显示

正确的SQL
此示例是外连接中的右外连接

// SQL 99语法
select p.name, d.deptname as '部门名称' from person p right join dept d on p.depId = d.id;

// outer 可以省略
select p.name, d.deptname as '部门名称' from person p right outer join dept d on p.depId = d.id;

左边表-人员表 person，没有匹配到的部门名称以null 进行匹配，右边的部门表 -dept 完全展示

示例5： 找出每个员工对应的领导名，要求显示所有员工

分析：

每个员工—person表
领导名，领导也是公司员工也是 person 表
显示所有员工，那就是 外连接，因为有的员工，比如老板，他就没有上级领导，他就是最大的

// 这是左外连接   也可以写成右外连接的形式
select p.name as '员工名', l.name as '领导名' from person p left join person l on p.leaderid = l.id;

select p.name as '员工名', l.name as '领导名' from person l right join person p on p.leaderid = l.id;

总结：

左外连接 left join 就是以join 左边的表为主，左边的表A完全显示， B表是否都显示，不一定
右外连接 right join 就是以 join 右边的表为主，右边的表完全显示，左边表的数据不一定都显示
对于左连接或者右连接，没有匹配到的数据会以null 显示
任何一个左连接都可以写成右连接，同样，任何一个右连接也可以写成左连接
外连接的 outer ，以及内连接的 inner 可以省略不写
区分SQL是内连接还是外连接，就是依靠SQL中有没有 left 或 right，加上inner 和outer 代码可读性强

如何区分外连接和内连接？

就看语法中，有没有 left 或者 right 关键字，有的话就是外连接，没有就是内连接

内连接和外连接的区别

内连接： A表和B表能够完全匹配的记录，都查询出来
外连接： A表和B表能够匹配的记录查询出来之外，还将其中一张表的记录，无条件的查询出来，对方没有匹配的记录，会自动模拟出null 与之匹配，这种就是外连接

外连接的查询结果条数 >= 内连接的查询结果条数

3. 全连接

多张表查询

案例6： 找出每个员工对应的部门名称，以及该员工对应的薪资等级，要求显示员工名，部门名，工资等级

分析：
每个员工—对应 person 表
每个员工的部门 — 对应 dept 部门表
薪资等级 — 对应 salgrade 薪酬范围表

员工的 deptId 对应 dept 表中的 id
员工的薪资 salary 对应属于 salgrade的 losal 和 hisal 区间
select p.name, p.salary, d.deptname from
person p left join dept d on p.depId = d.id;

select p.name, p.salary, d.deptname, s.grade from
person p left join dept d on  p.depId = d.id
left join salgrade s on
p.salary between s.losal and s.hisal;

这个始终是以 person 为主的， peron表有15条记录最终通过连接查询，仍旧返回15条记录

多张表进行连接，格式

select 
            XXXXX
from
            a表
join
            b表
on
            a.的条件 = b的条件
join
            c表
on
            a的条件 = c的条件

文字描述：
a表和 b表进行连接之后， a表再和c表进行连接