彻底理解SQL中的JOIN

Love The Way You Lie 2024-03-16 21:28 14阅读 0赞

### 数据准备 ###

-- 建表语句
    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 DEFAULT CHARSET=utf8;
    
    
    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 DEFAULT CHARSET=utf8;
    
    -- 插入数据
    INSERT INTO tbl_dept (deptName, locAdd) VALUES ('工程部', '11');
    INSERT INTO tbl_dept (deptName, locAdd) VALUES ('财务部', '12');
    INSERT INTO tbl_dept (deptName, locAdd) VALUES ('开发部', '13');
    INSERT INTO tbl_dept (deptName, locAdd) VALUES ('营销部', '14');
    INSERT INTO tbl_dept (deptName, locAdd) VALUES ('人事部', '15');
    
    
    INSERT INTO tbl_emp (name, deptId) VALUES ('z3', '1');
    INSERT INTO tbl_emp (name, deptId) VALUES ('z4', '1');
    INSERT INTO tbl_emp (name, deptId) VALUES ('z5', '1');
    INSERT INTO tbl_emp (name, deptId) VALUES ('w5', '2');
    INSERT INTO tbl_emp (name, deptId) VALUES ('w6', '2');
    INSERT INTO tbl_emp (name, deptId) VALUES ('s7', '3');
    INSERT INTO tbl_emp (name, deptId) VALUES ('s8', '4');
    INSERT INTO tbl_emp (name, deptId) VALUES ('s9', '51');
    
    # 确认数据插入完成
    SELECT * FROM `tbl_emp`;
    SELECT * FROM `tbl_dept`;

![ba48363aa0e04d529c83e81513004369.png][]![b97b56451b5f4993b9ac28b1327a6cc7.png][]

#### **1、 inner join** ####

![f09e511b6fa640aba82410a6020a094b.png][]

# inner join 结果显示a表和b表共有的部分
    SELECT *
    FROM tbl_emp a
    INNER JOIN tbl_dept b ON b.id = a.deptId

![753484a9ed764f05a4e9b61de17a5584.png][]

####  **2、left join** ####

![210b62261d0b475faa0dde0fd409af31.png][]

# left join 结果显示a表和b表共有的部分+a表独有的部分
    SELECT *
    FROM  tbl_emp a
    LEFT JOIN tbl_dept b ON b.id = a.deptId;

![6ec12cd02bdf45ef91e2025b38f7b7c5.png][]

#### 3、right join ####

![a98b30caa2684d458b2fc57f971d698e.png][]

# right join 结果显示a表和b表共有的部分+b表独有的部分
    SELECT * 
    FROM tbl_emp  a 
    RIGHT JOIN tbl_dept b ON b.id=a.deptId

![60a155a617334d898cecb7f5c69c90f9.png][]

#### 4、left join + where ####

![3862011a2d1844a4a45b893ba1efab15.png][]

# left join + where  结果显示为a独有部分，与left join的区别在于去掉了a表与b表的共有部分
    SELECT * 
    FROM tbl_emp  a 
    LEFT JOIN tbl_dept b ON b.id = a.deptId 
    WHERE b.id IS NULL

![21c9bac0ac4647e6b162fe392ef26a59.png][]

#### 5、right join + where ####

![0bb78c3a481b411c91256cc7f308bf80.png][]

# right join + where 结果显示为b独有部分，与right join的区别在于去掉了a表与b表的共有部分
    SELECT * 
    FROM tbl_emp  a 
    RIGHT JOIN tbl_dept b ON b.id= a.deptId
    WHERE a.id IS NULL

![01e2f2df67d1498d9088cd2e43c39d6e.png][]

#### 6、full outer join ####

![5a7ed627912f4727ab361053616bb987.png][]

# full outer join 结果显示：a表与b表共有部分 + a表独有部分 + b表独有部分
    # mysql不支持full outer join语法 oracle支持此语法
    SELECT * 
    FROM tbl_emp  a 
    FULL OUTER JOIN tbl_dept b ON  b.id=a.deptId

# 解决办法:union(合并+去重)
    SELECT * FROM tbl_emp  a LEFT JOIN tbl_dept b ON  b.id = a.deptId
    UNION 
    SELECT * FROM tbl_emp  a RIGHT JOIN tbl_dept b ON b.id = a.deptId

![4767a96346c447a498651f0892a80aa9.png][]

#### 7、full outer join+where ####

![c707acbeb65c4d7aa8d4f59e109be44f.png][]

# full outer join + where  结果显示： a表独有部分 + b表独有部分
    # mysql不支持full outer join语法 oracle支持此语法
    SELECT *
    FROM tbl_emp a
    FULL OUTER JOIN tbl_dept b  ON  b.id = a.deptId
    WHERE b.id IS NULL OR a.id is NULL

SELECT * FROM tbl_emp  a LEFT JOIN tbl_dept b ON  b.id = a.deptId WHERE b.id IS NULL
    UNION
    SELECT * FROM tbl_emp  a RIGHT JOIN tbl_dept b ON b.id = a.deptId WHERE a.id  IS NULL

![8bbfd5edc1ae46e3ad5bd0922b28ccb9.png][]

[ba48363aa0e04d529c83e81513004369.png]: https://img-blog.csdnimg.cn/ba48363aa0e04d529c83e81513004369.png
[b97b56451b5f4993b9ac28b1327a6cc7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/84aeb6a56a8f42f0a93b9926a8fa5e1a.png
[f09e511b6fa640aba82410a6020a094b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/69d47c3e7ee44fe38cfc7c2c738d20ed.png
[753484a9ed764f05a4e9b61de17a5584.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/e081a078517e4d74ade8ac7aee568571.png
[210b62261d0b475faa0dde0fd409af31.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/01682ad8e3484e12b1134a1258d9d438.png
[6ec12cd02bdf45ef91e2025b38f7b7c5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/36e04ac930b94ad7b56d17968ebf6641.png
[a98b30caa2684d458b2fc57f971d698e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/52a1230fa86946a6bee0867f0a2c32ad.png
[60a155a617334d898cecb7f5c69c90f9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/27f64ca7e3da4ac2804023fa7bec1d36.png
[3862011a2d1844a4a45b893ba1efab15.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/2cb1b1a742894aea9b58a9f6e66f0335.png
[21c9bac0ac4647e6b162fe392ef26a59.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/2096d06b17804dc49807161135076b22.png
[0bb78c3a481b411c91256cc7f308bf80.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/f5ef1036631b419899eae9c5f0e58f79.png
[01e2f2df67d1498d9088cd2e43c39d6e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/5f314a0d86d244598a0ce3a72ab3029b.png
[5a7ed627912f4727ab361053616bb987.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/b8f8129c927b41e199c09ec418ba1f69.png
[4767a96346c447a498651f0892a80aa9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/b66119d88ab9426ba4e655cd50bdfb95.png
[c707acbeb65c4d7aa8d4f59e109be44f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/b0754ea09ec04020b98cc745e2d6aed7.png
[8bbfd5edc1ae46e3ad5bd0922b28ccb9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/47d3c24105fd46a795611bfa0bc1ee6b.png