MySQL--七种JOIN理论详解

╰+攻爆jí腚メ 2021-08-13 16:15 436阅读 0赞

#### 七种JOIN理论 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]  
**1. INNER JOIN 内连接**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 1]  
**2. LEFT JOIN 左连接**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 2]

**3. RIGHT JOIN 右连接**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 3]

**4. OUTER JOIN 外连接**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 4]

**5. LEFT JOIN EXCLUDING INNER JOIN 左连接-内连接**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 5]

**6. RIGHT JOIN EXCLUDING INNER JOIN 右连接-内连接**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 6]

**7. OUTER JOIN EXCLUDING INNER JOIN 外连接-内连接**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 7]

#### 测试 ####

建表

CREATE TABLE tbl_dep(id INT(11) NOT NULL AUTO_INCREMENT,
    deptName VARCHAR(22) DEFAULT NULL,
    addr VARCHAR(22) DEFAULT NULL,
    PRIMARY KEY(id)
    )ENGINE = INNODB DEFAULT CHARSET=utf8;
    
    CREATE TABLE tbl_emp(
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(22) DEFAULT NULL,
    `deptId` VARCHAR(22) DEFAULT NULL,
    PRIMARY KEY (`id`),
    #constraint `fk_deptId` foreign key (`deptId`) references tbl_dep(id);
    )engine=innodb default charset =utf8;

插入数据

INSERT INTO tbl_dep(deptName,addr) VALUES('hr','111');
    INSERT INTO tbl_dep(deptName,addr) VALUES('bd','112');
    INSERT INTO tbl_dep(deptName,addr) VALUES('vb','113');
    INSERT INTO tbl_dep(deptName,addr) VALUES('sd','114');
    INSERT INTO tbl_dep(deptName,addr) VALUES('yy','115');
    
    INSERT INTO tbl_emp(name,deptId) VALUES('k8',1); 
    INSERT INTO tbl_emp(name,deptId) VALUES('k6',2); 
    INSERT INTO tbl_emp(name,deptId) VALUES('k4',3); 
    INSERT INTO tbl_emp(name,deptId) VALUES('k4',11);

**七种join理论。**

**内连接(两表的共有部分)**

SELECT * FROM tbl_dep d INNER JOIN tbl_emp e ON d.id=e.deptId;

**左连接（左表的全部，右表不满足补NULL）**

SELECT * FROM tbl_dep d LEFT JOIN tbl_emp e ON d.id=e.deptId;

**右连接（右表的全部，左表不满足的补NULL）**

SELECT * FROM tbl_dep d RIGHT JOIN tbl_emp e ON d.id=e.deptId;

**特殊的左连接，（显示为左表的独有的数据）**

说明：查询tbl_dep 表中跟tbl_emp 表无关联关系的数据，即tbl_dep 独占，且tbl_emp 表的显示列补NULL；
    SELECT * FROM tbl_dep d LEFT JOIN tbl_emp e ON d.id=e.deptId WHERE e.deptId IS NULL;

**特殊的右连接（显示为右表的独有的数据 ）**

说明：查询tbl_emp 表中跟tbl_dep 表无关联关系的数据，即tbl_emp 独占，且tbl_dep 表的显示列补NULL；
    SELECT * FROM tbl_dep d RIGHT JOIN tbl_emp e ON d.id=e.deptId WHERE d.id IS NULL;

**全连接（显示全部数据）（mysql 不支持 full outer join）**

UNION ：有去重的功能。
    SELECT * FROM tbl_dep d LEFT JOIN tbl_emp e ON d.id=e.deptId UNION
    SELECT * FROM tbl_dep d RIGHT JOIN tbl_emp e ON d.id=e.deptId;

**显示两表的独有的数据**

SELECT * FROM tbl_dep d LEFT JOIN tbl_emp e ON d.id=e.deptId WHERE e.deptId IS NULL UNION
    SELECT * FROM tbl_dep d RIGHT JOIN tbl_emp e ON d.id=e.deptId WHERE d.id IS NULL;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]: /images/20210813/262631884002413f825d6c3c95d3a828.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 1]: /images/20210813/283f71ee214445f8a02444d05d43dfb8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 2]: /images/20210813/79ece9e727e64bf782888be773248046.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 3]: /images/20210813/a3cd5c9d4d324025a5df1fe00ea22483.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 4]: /images/20210813/38214154eed241e0828943a6bf77392d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 5]: /images/20210813/e60e566989664c97b3cc757f0215abe5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 6]: /images/20210813/78c4c4a8d6774e838ef57b55e0c6629d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 7]: /images/20210813/d80054aed4da4ce5b4a6970c2f114b01.png