连接
七种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;
文章来源：https://www.toymoban.com/news/detail-758128.html
文章说明
本文章为本人学习尚硅谷的学习笔记，文章中大部分内容来源于尚硅谷视频（点击学习尚硅谷相关课程），也有部分内容来自于自己的思考，发布文章是想帮助其他学习的人更方便地整理自己的笔记或者直接通过文章学习相关知识，如有侵权请联系删除，最后对尚硅谷的优质课程表示感谢。文章来源地址https://www.toymoban.com/news/detail-758128.html
到了这里，关于【MYSQL高级】Mysql 表的七种连接方式【附带练习sql】的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
