【MySQL高级】MySQL表的七种连接方式【附带练习sql】

04-08 6769阅读 0评论

文章目录

连接

七种JOIN介绍
练习

建表+存储数据
笛卡尔积
内连接
左连接
右连接
A表独有
B表独有
全连接

实现方式一：左连接+右连接+公共部分去重
实现方式二：左连接+B独有
A独有+B独有

文章说明

连接

七种JOIN介绍

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