7种JOIN的SQL的编写

古城微笑少年丶 2022-11-17 05:25 204阅读 0赞

## SQL连接（JOIN） ##

SQL JOIN用于把来自两个或者多个表的行结合起来

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUT JOIN相关的7种用法

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzcyNTUxNw_size_16_color_FFFFFF_t_70][]

可以按照序号顺序看，更好理解

1、是两者的共有部分，2、是两者的共有部分+A独有的部分（简称全A）

3、是两者的共有部分+B独有部分（简称全B），4、是2的去除共有部分，就加上where B.key IS NULL（独A）

5、是3的去除共有部分，就加上where A.key IS NULL（独B）； 6、是全连接；7、是全连接除去共有部分

## SQL编写 ##

### 数据库准备 ###

CREATE TABLE `tbl_emp` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(20) DEFAULT NULL,
    `deptId` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`) ,
    KEY `fk_dept_id`(`deptId`)
    )ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;
    
    CREATE TABLE `tbl_dept` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `deptName` varchar(30) DEFAULT NULL,
    `locAdd` varchar(40) DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;
    
    insert  into `tbl_dept`(`id`,`deptName`,`locAdd`) values (1,'RD','11'),(2,'HR','12'),(3,'MK','13'),(4,'MIS','14'),(5,'FD','15');
    
    insert  into `tbl_emp`(`id`,`name`,`deptId`) values (1,'Z3',1),(2,'Z4',1),(3,'Z5',1),(4,'W5',2),(5,'W6',2),(6,'W7',2),(7,'S7',3),(8,'S8',4),(9,'S9',51);

### 实操 ###

1、两者的共有部分

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzcyNTUxNw_size_16_color_FFFFFF_t_70 1][]

SELECT * FROM tbl_dept dept INNER JOIN tbl_emp emp ON dept.`id`=emp.`deptId`;

查询后的截图：只有两者的共有部分

![20210408182429607.png][]

2、两者的共有部分+A独有的部分（简称全A）

左边的表有匹配就照常显示，没有满足的直接补null值。

![20210408183109253.png][]

SELECT * FROM tbl_dept dept LEFT JOIN tbl_emp emp ON dept.`id`=emp.`deptId`;

![202104081832098.png][]

3、两者的共有部分+B独有部分（简称全B）

右边的表又匹配的就照常显示，没有的就直接补null值填充。

![202104081835546.png][]

SELECT * FROM tbl_dept dept RIGHT JOIN tbl_emp emp ON dept.`id`=emp.`deptId`;

![20210408183527887.png][]

4、2的去除共有部分，就加上where B.key IS NULL（独A）

![20210408183740149.png][]

SELECT * FROM tbl_dept dept LEFT JOIN tbl_emp emp ON dept.`id`=emp.`deptId` WHERE emp.`deptId`IS NULL;

![20210408183844577.png][]

就是A的独有部分，没有就补null值

5、全连接

![20210408184142766.png][]

SELECT * FROM tbl_dept dept FULL OUTER JOIN tbl_emp emp ON dept.`id`=emp.`deptId`;

![20210408184545587.png][]

MySQL不支持full outer join 这样的语法 ，但是在oracle可以适用。

可以拼接并去重：

SELECT * FROM tbl_dept dept LEFT JOIN tbl_emp emp ON dept.`id`=emp.`deptId`
    UNION
    SELECT * FROM tbl_dept dept RIGHT JOIN tbl_emp emp ON dept.`id`=emp.`deptId`

6、全连接除去共有部分

SELECT * FROM tbl_dept dept LEFT JOIN tbl_emp emp ON dept.`id`=emp.`deptId` WHERE emp.`deptId`IS NULL
    UNION
    SELECT * FROM tbl_dept dept RIGHT JOIN tbl_emp emp ON dept.`id`=emp.`deptId` WHERE dept.`id`IS NULL

![20210408185524193.png][]

**总结：**

**要查询的信息来自哪个表就哪个表就是主表**

1、外连接的查询结果为主表中的所有记录

*     如果从表中没有和它匹配的，则显示匹配的值
 *  如果从表中没有和它匹配的，则显示null值

2、左外连接 ： **left  join 左边的是主表**

3、右外连接 ： **right join 右边的是主表**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzcyNTUxNw_size_16_color_FFFFFF_t_70]: /images/20221022/7927e70e9e8e4ee88f6d292050accebd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzcyNTUxNw_size_16_color_FFFFFF_t_70 1]: /images/20221022/8bbaa2d18e094612a132a6410d3e48fc.png
[20210408182429607.png]: /images/20221022/639a386ea67143f686fb5286713a125f.png
[20210408183109253.png]: /images/20221022/0a99cb00adbb4086b7075fffd588ee77.png
[202104081832098.png]: /images/20221022/9aede8aa47b64c76becdcdf199b074f7.png
[202104081835546.png]: /images/20221022/e0b666de592e497a8c4e6730527d2b04.png
[20210408183527887.png]: /images/20221022/1b36ba134e3e4a978f450b206e707ee7.png
[20210408183740149.png]: /images/20221022/ecec28f500bd4190ba6b52926686a8c3.png
[20210408183844577.png]: /images/20221022/9462c7e0589a47b997d3ae64a89e1340.png
[20210408184142766.png]: /images/20221022/3e9d30b626234016bde08ca150a9c23c.png
[20210408184545587.png]: /images/20221022/9cb4cca30b594991a9ee393a42abb6f5.png
[20210408185524193.png]: /images/20221022/64cbae340a004f4d8987687109c4a335.png