史上最全 MySQL JOIN 查询

小咪咪 2022-05-10 12:12 311阅读 0赞

建表  
在这里呢我们先来建立两张有外键关联的张表。

CREATE DATABASE db0206;
    	USE db0206;
    	
    	CREATE TABLE `db0206`.`tbl_dept`(  
    	  `id` INT(11) NOT NULL AUTO_INCREMENT,
    	  `deptName` VARCHAR(30),
    	  `locAdd` VARCHAR(40),
    	  PRIMARY KEY (`id`)
    	) ENGINE=INNODB CHARSET=utf8;
    	
    	CREATE TABLE `db0206`.`tbl_emp`(  
    	  `id` INT(11) NOT NULL AUTO_INCREMENT,
    	  `name` VARCHAR(20),
    	  `deptId` INT(11),
    	  PRIMARY KEY (`id`),
    	  FOREIGN KEY (`deptId`) REFERENCES `db0206`.`tb_dept`(`id`)
    	) ENGINE=INNODB CHARSET=utf8;
    	/*插入数据*/
    	INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);
    	INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);
    	INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);
    	INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);
    	INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',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);

内连接  
内连接文氏图  
![在这里插入图片描述][70]

执行的sql语句

select * from tbl_dept a inner join tbl_emp b on a.id=b.deptId;

查询结果  
![这里写图片描述][70 1]  
左外连接  
左外连接文氏图

![在这里插入图片描述][70 2]

执行的sql语句

select * from tbl_dept a left join tbl_emp b on a.id=b.deptId;

![这里写图片描述][70 3]

右外连接  
右外连接文氏图  
![在这里插入图片描述][70 4]

执行的sql语句

select * from tbl_dept a right join tbl_emp b on a.id=b.deptId;

![在这里插入图片描述][70 5]

左连接  
左连接文氏图  
![这里写图片描述][70 6]

执行的sql语句

select * from tbl_dept a left join tbl_emp b on a.id=b.deptId where b.deptId is null;

查询结果  
![这里写图片描述][70 7]

右连接  
右连接文氏图  
![右连接][70 8]

执行的sql语句

select * from tbl_dept a right join tbl_emp b on a.id=b.deptId where a.id is null;

![在这里插入图片描述][70 9]

全连接  
全连接文氏图  
![这里写图片描述][70 10]

执行的sql语句

select * from tbl_dept a right join tbl_emp b on a.id=b.deptId 
    union 
    select * from tbl_dept a left join tbl_emp b on a.id=b.deptId;

查询结果  
![在这里插入图片描述][70 11]  
全连接  
两张表中都没有出现的数据集  
![文氏图][70 12]

执行的sql语句

select * from tbl_dept a right join tbl_emp b on a.id=b.deptId where a.id is null 
    union 
    select * from tbl_dept a left join tbl_emp b on a.id=b.deptId where b.deptId is null;

查询结果  
![这里写图片描述][70 13]

[70]: /images/20220510/e47024c332c248ac92fdb0662ae7911f.png
[70 1]: /images/20220510/f90f9ff414aa497da266a1a8b586f011.png
[70 2]: /images/20220510/47ed5690761a462f936512f5bd98204c.png
[70 3]: /images/20220510/64c477029fb440f798ba1b9754d3100b.png
[70 4]: /images/20220510/78eb1ed50c204c49aa3fdcc3b2a8b48d.png
[70 5]: /images/20220510/21ac4652b436421c8e8df2749c317db4.png
[70 6]: /images/20220510/f7bbb3c087fc4cf1a03eb84f3f3e2dea.png
[70 7]: /images/20220510/030ba3dc438d4deab89ab03c3bec9b23.png
[70 8]: /images/20220510/2048b80272fe45098f462ec493790f07.png
[70 9]: /images/20220510/2d6493780d9747b6b32287a7786c80fc.png
[70 10]: /images/20220510/4c4dac667048401592ce3ba48c31e86f.png
[70 11]: /images/20220510/2c9ae9bc43ec46a7b55c1842fe467027.png
[70 12]: /images/20220510/7833d9f2188948a79f7d09267cd93aa6.png
[70 13]: /images/20220510/25075db2ab4946578f99e07287838abb.png