【MySQL高级】MySQL表的七种连接方式【提供演示SQL】

---

🌟“场快订” 场馆预定平台是我个人匠心打造的全栈免费开源项目，使用 Spring Cloud + Uniapp 开发，包含高并发设计（缓存击穿、缓存穿透处理）、大数据量查询优化、分库分表、IP 流量管控、分布式事务、分布式 ID、幂等处理、WebSocket 双向通信、消息队列异步执行、延时队列等内容，此外还包括域名购买与解析、项目打包上线、HTTP升级HTTPS等手把手教程，项目代码简洁，部分代码使用设计模式重构，非常适用于学习后端技术、毕业设计、相关计算机竞赛，感兴趣的朋友可以从以下链接进行学习：

📦 源码仓库：https://gitee.com/HelloDam/venue-reservation
📚 技术专栏：https://blog.csdn.net/laodanqiu/category_12877044.html
📱 在线体验：https://hellodam.website（建议手机访问）

---

🎯 本文详细介绍了SQL中七种JOIN的使用方法与应用场景，包括内连接、左连接、右连接、全连接以及独有部分的查询方式。通过具体示例和图形化展示，清晰解释了每种连接的结果及其SQL语句的编写方法。文章还提供了建表及数据插入的SQL代码，并通过实际操作演示了笛卡尔积、内连接、外连接等查询结果，帮助读者更好地理解连接的原理与实现。此外，针对MySQL不支持FULL OUTER JOIN的情况，提供了替代解决方案，如通过LEFT JOIN与RIGHT JOIN结合实现全连接效果。内容实用，适合SQL学习者深入掌握连接查询技巧。

七种JOIN介绍

图形	连接方式	说明	SQL
	内连接	共有部分	SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key;
	左连接	A表独有+共有部分	SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key;
	右连接	B表独有+共有部分	SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;
		A的独有	SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;
		B的独有	SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;
	全连接	A的独有+共有部分+B的独有	SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key; MySQL不支持FULL OUTER JOIN这种语法
		A独有+B独有	SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL OR B.Key IS NULL; MySQL不支持FULL OUTER JOIN这种语法

练习

建表+存储数据

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(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);
insert into tbl_emp(NAME,deptId) values('s9',51);

笛卡尔积

select * from tbl_emp,tbl_dept;

5条数据和8条数据两两组合，一共40条，结果如下

"id"       "name"     "deptId"    "id"      "deptName"   "locAdd"
"1"        "z3"        "1"        "5"        "FD"        "15"
"1"        "z3"        "1"        "4"        "MIS"        "14"
"1"        "z3"        "1"        "3"        "MK"        "13"
"1"        "z3"        "1"        "2"        "HR"        "12"
"1"        "z3"        "1"        "1"        "RD"        "11"
"2"        "z4"        "1"        "5"        "FD"        "15"
"2"        "z4"        "1"        "4"        "MIS"        "14"
"2"        "z4"        "1"        "3"        "MK"        "13"
"2"        "z4"        "1"        "2"        "HR"        "12"
"2"        "z4"        "1"        "1"        "RD"        "11"
"3"        "z5"        "1"        "5"        "FD"        "15"
"3"        "z5"        "1"        "4"        "MIS"        "14"
"3"        "z5"        "1"        "3"        "MK"        "13"
"3"        "z5"        "1"        "2"        "HR"        "12"
"3"        "z5"        "1"        "1"        "RD"        "11"
"4"        "w5"        "2"        "5"        "FD"        "15"
"4"        "w5"        "2"        "4"        "MIS"        "14"
"4"        "w5"        "2"        "3"        "MK"        "13"
"4"        "w5"        "2"        "2"        "HR"        "12"
"4"        "w5"        "2"        "1"        "RD"        "11"
"5"        "w6"        "2"        "5"        "FD"        "15"
"5"        "w6"        "2"        "4"        "MIS"        "14"
"5"        "w6"        "2"        "3"        "MK"        "13"
"5"        "w6"        "2"        "2"        "HR"        "12"
"5"        "w6"        "2"        "1"        "RD"        "11"
"6"        "s7"        "3"        "5"        "FD"        "15"
"6"        "s7"        "3"        "4"        "MIS"        "14"
"6"        "s7"        "3"        "3"        "MK"        "13"
"6"        "s7"        "3"        "2"        "HR"        "12"
"6"        "s7"        "3"        "1"        "RD"        "11"
"7"        "s8"        "4"        "5"        "FD"        "15"
"7"        "s8"        "4"        "4"        "MIS"        "14"
"7"        "s8"        "4"        "3"        "MK"        "13"
"7"        "s8"        "4"        "2"        "HR"        "12"
"7"        "s8"        "4"        "1"        "RD"        "11"
"8"        "s9"        "51"        "5"        "FD"        "15"
"8"        "s9"        "51"        "4"        "MIS"        "14"
"8"        "s9"        "51"        "3"        "MK"        "13"
"8"        "s9"        "51"        "2"        "HR"        "12"
"8"        "s9"        "51"        "1"        "RD"        "11"

内连接

SELECT * FROM tbl_emp a INNER JOIN tbl_dept b ON a.deptId = b.id;

左连接（外连接）

SELECT * FROM tbl_emp a LEFT JOIN tbl_dept b ON a.deptId = b.id;

右连接（外连接）

SELECT * FROM tbl_emp a RIGHT JOIN tbl_dept b ON a.deptId = b.id;

A表独有

SELECT * FROM tbl_emp a LEFT JOIN tbl_dept b ON a.deptId = b.id WHERE b.id is null;

B表独有

SELECT * FROM tbl_emp a RIGHT JOIN tbl_dept b ON a.deptId = b.id WHERE a.deptId is null;

全连接

尝试换一种方式实现：A的独有+共有+B的独有

实现方式一：左连接+右连接+公共部分去重

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

实现方式二：左连接+B独有

SELECT * FROM tbl_emp a LEFT JOIN tbl_dept b ON a.deptId = b.id 
UNION 
SELECT * FROM tbl_emp a RIGHT JOIN tbl_dept b ON a.deptId = b.id WHERE a.deptId is null;

A独有+B独有

SELECT * FROM tbl_emp a LEFT JOIN tbl_dept b ON a.deptId = b.id WHERE b.id is null 
UNION 
SELECT * FROM tbl_emp a RIGHT JOIN tbl_dept b ON a.deptId = b.id WHERE a.deptId is null;