mysql join查询

傷城~ 2024-02-19 20:47 16阅读 0赞

--------------------

#### 0.准备 ####

join主要根据两表或多表之间列的关系，从这些表中进行数据的查询。

首先创建两张表：tb\_emp(员工表)和tb\_dept(部门表)，并插入相关测试数据。

1.tb\_emp表。

DROP TABLE IF EXISTS `tb_emp`;
    CREATE TABLE `tb_emp` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(20) NOT NULL,
      `deptid` int(11) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_tb_emp_name` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO `tb_emp`(name,deptid) VALUES ('jack', '1');
    INSERT INTO `tb_emp`(name,deptid) VALUES ('tom', '1');
    INSERT INTO `tb_emp`(name,deptid) VALUES ('tonny', '1');
    INSERT INTO `tb_emp`(name,deptid) VALUES ('mary', '2');
    INSERT INTO `tb_emp`(name,deptid) VALUES ('rose', '2');
    INSERT INTO `tb_emp`(name,deptid) VALUES ('luffy', '3');
    INSERT INTO `tb_emp`(name,deptid) VALUES ('outman', '14');

2.tb\_dept表。

DROP TABLE IF EXISTS `tb_dept`;
    CREATE TABLE `tb_dept` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `deptname` varchar(20) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO `tb_dept`(deptname) VALUES ('研发');
    INSERT INTO `tb_dept`(deptname) VALUES ('测试');
    INSERT INTO `tb_dept`(deptname) VALUES ('运维');
    INSERT INTO `tb_dept`(deptname) VALUES ('经理');

从上表插入的数据可知outman是没有对应部门的。

*  交叉连接: 生成笛卡尔积，它不使用任何匹配条件,
 *  内连接: 只连接匹配的行
 *  左连接: 会显示左边表内所有的值，无论在右边的表内是否匹配
 *  右连接: 会显示右边表内所有的值，无论在左边的表内是否匹配
 *  全外连接: 包含左右两个表的全部行

#### 1.inner join ####

注：A表示左表，B表示右表，下同。

inner join：A、B共有，也就是交集。

满足某一条件的A，B内部的数据；正因为得到的是内部共有数据，所以连接方式称为内连接。

![706569-20180621102153321-1096428769.png][]

![706569-20180621102459561-1669926910.png][]

#### 2.left join ####

left jion：A独有+AB共有（交集）

这种场景下得到的是A的所有数据，和满足某一条件的B的数据。

![706569-20180621102924891-768238348.png][]

![706569-20180621103346416-943998578.png][]

#### 3.right join ####

right join：B独有+AB共有（交集）

![706569-20180621103759640-303056357.png][]

![706569-20180621103718653-1396393882.png][]

#### 4.A独有 ####

![706569-20180621104329421-109433734.png][]

![706569-20180621104545003-582468281.png][]

注：参照left join，A独有只是将AB交集部分去掉。

#### 5.B独有 ####

![706569-20180621104744833-2072459534.png][]

![706569-20180621104921428-1659279197.png][]

注：参照right join，B独有只是将AB交集部分去掉。

#### 6.AB全有（并集） ####

![706569-20180621110144548-704012923.png][]

由于mysql中不支持full outer join，所以这里通过union进行转换。AB并集：AB交集+A独有+B独有。

![706569-20180621110545977-2083791147.png][]

#### 7.A、B独有并集 ####

![706569-20180621110919839-869053949.png][]

A、B独有并集，相当于A、B全有去掉AB的共有（交集）。

![706569-20180621111429409-1493367532.png][]

#### 总结 ####

这里主要对MySQL中join语句的7中用法进行了总结，主要注意MySQL不支持full outer join，所以需要对其进行转换变形，最终达到效果

[706569-20180621102153321-1096428769.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/003cc2b4f91d4dbd94db4df1d17c50bd.png
[706569-20180621102459561-1669926910.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/eca2bc55504e48729e581bf1efe96851.png
[706569-20180621102924891-768238348.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/c7cae32669ac497eaac3699fb93c9659.png
[706569-20180621103346416-943998578.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/f63cceac1dab487cb875fa1dc42bfbd8.png
[706569-20180621103759640-303056357.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/003a3694049c4e93a59bd6431dee32de.png
[706569-20180621103718653-1396393882.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/fc22eae801ee4fc8aaf395f9f0b383fa.png
[706569-20180621104329421-109433734.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/341bec4cd9e149b7853e761684f2bbe7.png
[706569-20180621104545003-582468281.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/ca539f3d2db44a5fb5bb83eef58745a9.png
[706569-20180621104744833-2072459534.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/2df3b1acc67443a1a98d8236093ea1d6.png
[706569-20180621104921428-1659279197.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/89be2201a12b4aa4bac11a2f6314a801.png
[706569-20180621110144548-704012923.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/21c1edbfd877477f923968c26d72dc28.png
[706569-20180621110545977-2083791147.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/3e836737ebf84967bcac6910a9ef72ef.png
[706569-20180621110919839-869053949.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/5612b75ae6bf4e38a8f3af67b510b555.png
[706569-20180621111429409-1493367532.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/617e170e1183441b82de84c809776767.png