一、join图
image.png二、sql语句准备
CREATE TABLE `t_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
empno int not null,
PRIMARY KEY (`id`),
KEY `idx_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
# 数据
INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('乔峰',35,2,100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',18,null,100010);
部门表数据
员工表数据
员工表
三、七种连接方式详解
1、内连接 inner join
内连接inner join表示A表和B表的共有部分数据。
内连接 inner join
select * from t_emp e INNER JOIN t_dept d on e.deptId = d.id;
inner join
2、左连接 left join
左连接 left join 表示A表和B表的公共部分,再加上A表的独有部分。
左连接 left join
select * from t_emp e left JOIN t_dept d on e.deptId = d.id;
left join
3、右连接 right join
右连接right join 表示A表和B表公共部分,在加上B表的独有部分。
右连接right join
select * from t_emp e right JOIN t_dept d on e.deptId = d.id;
right join
4、A表独有
查找A表独有部分,则需查找A表和B表的共有部分并加上A表的独有部分,在将A表和B表的共有部分剔除即可(也就是挑选B的主键为空的数据)。
A表独有
select * from t_emp e left JOIN t_dept d on e.deptId = d.id where d.id is null;
A表独有部分
5、B表独有
查找B表独有部分,则需查找A表和B表的共有部分并加上B表的独有部分,在将A表和B表的共有部分剔除即可(也就是挑选A的主键为空的数据)。
B表独有
select * from t_emp e right JOIN t_dept d on e.deptId = d.id where e.deptId is null;
B表独有
6、全连接full outer join
全连接则表示将A表和B表的公共部分及A表、B表的独有部分,所有数据都查询出来
全连接full outer join
指导图的全连接 full outer join 在mysql 语法报错!但是可以通过union关键字进行查询。
UNION会把 重复的行去掉,返回的行都是唯一的。如果想保留重复行,可以使用 UNION ALL 关键字。
UNION其实就是将A表和B表的共有部分及A表的独有部分(即左连接left join)加上A、B表共有部分及B表的独有部分(即右连接right join)合并起来,并进行去重即可。
select * from t_emp e left join t_dept d on e.deptId = d.id
union
select * from t_emp e right join t_dept d on e.deptId = d.id;
全连接full outer join
7、A表独有和B表独有
查询A表独有部分并加上B表独有部分
A表独有和B表独有
实际就是查询A表的独有部分和B表的独有部分,使用UNION进行连接即可。
select * from t_emp e left join t_dept d on e.deptId = d.id where d.id is null
union
select * from t_emp e right join t_dept d on e.deptId = d.id where e.deptId is null;
A表独有和B表独有
网友评论