mysql 内连接、左连接、右连接和全连接

朱雀 2022-10-08 13:22 238阅读 0赞

## 前言： ##

以前涉及 sql 查询都是用很简单的查询语句，然后用代码逻辑进行数据筛选，倒是也没啥问题。后来随着表数量的增加，经常需要好几张表联合一起进行筛选查询，这时候就发现有一些问题：筛选逻辑涉及比较多，用代码进行筛选，代码增量几十行上百行，而且代码维护也比较麻烦。如果用 sql 语句进行筛选，只增加几行代码，后期也好维护。

## 一、从 from 多表查询开始说起 ##

看下面一条语句，

select * from emp,dept;

from 后面用逗号分隔的两张表查询跟题述要说的连接有什么异同吗？

先说说上述sql语句的情况：

from多表查询结果是笛卡尔积，

**笛卡尔积：指在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X×Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。**

概念有点抽象，举个例子：

集合A=\{a, b\}，集合B=\{0, 1, 2\}，则两个集合的笛卡尔积为\{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)\}

不是太懂的话，再举个数据表的例子：

上述sql涉及的两张表如下：

部门表：

CREATE TABLE `dept` (
      `id` int NOT NULL AUTO_INCREMENT,
      `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '部门名称',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

数据：

![20210621190344231.png][]

雇员表 ：emp

CREATE TABLE `emp` (
      `id` int NOT NULL AUTO_INCREMENT,
      `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '名称',
      `dept_id` int DEFAULT NULL COMMENT '部门id',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

数据：

![20210621174332683.png][]

执行sql语句结果如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FpbmdxdWFueWluZ3l1ZQ_size_16_color_FFFFFF_t_70][]

结果会显示两张数据表所有字段和所有组合，总数据条数=（emp数据条数 \* dept数据条数），**如果其中一张表为空，结果肯定是空，这个虽然很简单，但是经常被人忽略。**from 后面还可以跟where条件语句，条件语句就是在上述的表中再次筛选返回结果。如果三张表就是三张表的笛卡尔积（总数据条数是单个表数据条数相乘）

## 二、内连接 ##

内连接其实相对好理解，就是把多张表中重合的部分筛选出来，inner 可以省略，例如：

select * from dept inner join emp on emp.dept_id=dept.id;

执行结果：

![2021062119071025.png][]

## 三、左连接 ##

左连接是以 from 后所有表的最左边表（左表）为核心，先全部显示左表数据，而后根据关联关系补充后续信息，例如：

select * from dept left join emp on emp.dept_id=dept.id;

![20210621191305639.png][]

可以这么理解，先显示左表信息，而后根据左表信息逐条在其它表查询，如果满足条件（on条件）就在后面补充其它表信息（多条满足就显示多行）。

**如果是超过两张表，也是以左表为核心，依次从左到右的顺序进行连接。**

## 四、右连接 ##

跟左连接类似，只是以右表为核心（from 后所有表的最右边表），例如：

select * from emp right join dept on emp.dept_id=dept.id;

执行结果：

![2021062119182192.png][]

## 五、全连接 ##

有些博主说 mysql 没有全连接，然后把左连接和右连接用 union 连接在一起称为全连接，这个也没问题。但是 union 主要不是这么用的，简单说 union 就是做多张表的加法（union all 是纯粹的加法，union 是去重的加法，下面会举例），使用 union 时**字段名称可以不同，但是字段数量一定要相同**，例如：

select id,dept_id from emp union (select id,name from dept);

执行结果：

![20210621192442313.png][]

另外：union 会把相同的结果进行合并（也就是去重），union all 不会触发合并，例如：

select id from emp union (select id from dept);
    select id from emp union all (select id from dept);

两个结果分别：

![20210621192740121.png][]

![20210621192803724.png][]

[20210621190344231.png]: /images/20221005/0614e725c3f244d1b9ca48676a6010e2.png
[20210621174332683.png]: /images/20221005/114c3147e6ba4c0f87ed4f1bf380e2e5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FpbmdxdWFueWluZ3l1ZQ_size_16_color_FFFFFF_t_70]: /images/20221005/b068f622d2104c17847142942120536a.png
[2021062119071025.png]: /images/20221005/cc454efe0a7143e2bc1f7ba1d0faa834.png
[20210621191305639.png]: /images/20221005/1970543ceb184fe1ad1cd6e6599825fa.png
[2021062119182192.png]: /images/20221005/82a2185e3ad04f1ea18d46cee53d46ee.png
[20210621192442313.png]: /images/20221005/fea5ec03c14c43ec9d184779aadf3f82.png
[20210621192740121.png]: /images/20221005/740031418116478ea6a07acb0f05346c.png
[20210621192803724.png]: /images/20221005/10ed6d8ed0ea45628c6eab28453b33a6.png