Mysql中Join查询使用与原理

在Mysql中我们最终的一个操作就是查询，查询中又要当属join的应用，这里我们在SQL的层面介绍join的7中产寻区别于数据的覆盖范围，但是不同的DB他的join使用可能略有不同。eg：Mysql中是无法使用外连接查询。这个文章中的内容都来自与B站周阳老师，记录这个笔记主要也是为了加深一下自己的认识。

一、概述与建表语句

1、概述

在Mysql中如果对join的类型不说明则默认采用的内连接——inner join。关于join这几种情况的使用通过下面的这张图以及后边的示例语句就没有什么问题了，但是他在底层的机制会是怎么样呢？

join的连接查询语义是将两张表关联起来，综合两张表中的数据得到我们想要的数据。正常情况下我们知道每张表中都会对应多条数据，那么这两张表中的数据如何匹配起来让他们作为一组数据供我们使用呢？一种的最暴力的的就是让两张表做笛卡尔积，如果两表的数据分别为M、N那么最后产寻出来的数据就会使M*N。这通常不是我们想要的结果，如果我们给定条件那么Mysql就会按照指定的规则匹配数据，在匹配数据的过程中会有一张主表一张从表。为什么这样说呢？关联数据我们一定是按照一定的规则进行的，但是表中的数据都是在磁盘中的，我们就需要先把一张表中的数据读取出来，利用已经读取出来的数据作为条件再去匹配另一张表。

那么既然是按照条件匹配的那么就有能匹配上与不能匹配上两种情况，能够匹配上的自然需要正常显示，只显示匹配上的数据这就是inner join的情况。那么就还有一种情况就是我们把主表中的数据全部都显示出来，而有关从表中的数据匹配上的话就显示出来没有匹配上的话就不显示出来。单纯的left join、right join不再在where中添加任何条件的时候我们可以称之为只显示主表。这样的情况也是下图中最上面的两个展示的，他们的区别也是我们使用的left还是right，这两个关键字是可以相互替换的，他们可以告诉Mysql确定主表的规则：left join以left join左边的表为主表，right的反之。这里需要意识到的一点是这里确定左右并不是以on两边的区分，而是以表中的先后顺序。

没有匹配上的数据，他的所有列一定是空的，通过这个特性我们就可以在where中添加相应的条件实现对个别表独有数据的过滤。

个人理解的是就算我们使用了多个join实际上也是只确定了一个主表，其他的都是围绕这个主表展开，但是没有进一步验证。

下面是一种混合的情况，关于主表的确定是关键，我们可以通过explain来帮我们确定，只要是进行了全表扫描的那就是主表。

2、建表语句

CREATE TABLE `t_dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
CREATE TABLE `t_emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(20) DEFAULT NULL,
  `age` INT(3) DEFAULT NULL,
 `deptId` INT(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `fk_dept_id` (`deptId`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
INSERT INTO t_emp(NAME,age,deptId) VALUES('风清扬',90,1);
INSERT INTO t_emp(NAME,age,deptId) VALUES('岳不群',50,1);
INSERT INTO t_emp(NAME,age,deptId) VALUES('令狐冲',24,1);
INSERT INTO t_emp(NAME,age,deptId) VALUES('洪七公',70,2);
INSERT INTO t_emp(NAME,age,deptId) VALUES('乔峰',35,2);
INSERT INTO t_emp(NAME,age,deptId) VALUES('灭绝师太',70,3);
INSERT INTO t_emp(NAME,age,deptId) VALUES('周芷若',20,3);
INSERT INTO t_emp(NAME,age,deptId) VALUES('张三丰',100,4);
INSERT INTO t_emp(NAME,age,deptId) VALUES('张无忌',25,5);
INSERT INTO t_emp(NAME,age,deptId) VALUES('韦小宝',18,null);

二、SQL实例

1、内连接查询

-- 关联产寻中所说的独有应该以关联键对依据
-- 内连接，查询出来的是，关联键既在A又在B的
explain select d.id,d.deptName,e.`name`,t4.content,t3.content
from t_dept d
inner join t_emp e on d.id = e.deptId

2、左右连接查询只显示主表

-- 对部门进行左连接，这里产寻出来的是所有的部门以及部门对应的人员信息
-- 如果所在的部门没有对应的人员，那么人员信息则置空（因为主键没有对上
-- 那么对应的其他信息自然就也没有了）
select d.id,d.deptName,e.`name`
from t_dept d
left join t_emp e on d.id = e.deptId 

3、显示独有

-- 这句话产出的含义就是没有员工的部门
select d.id,d.deptName,e.`name`
from t_dept d
left join t_emp e on d.id = e.deptId
where e.deptId is null