join主要根据两表或多表之间列的关系,从这些表中进行数据的查询。
首先创建两张表:tb_emp(员工表)和tb_dept(部门表),并插入相关测试数据。
tb_emp表:
DROP TABLE IF EXISTS `tb_emp`;
CREATE TABLE `tb_emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`deptid` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_tb_emp_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `tb_emp`(name,deptid) VALUES ('jack', '1');
INSERT INTO `tb_emp`(name,deptid) VALUES ('tom', '1');
INSERT INTO `tb_emp`(name,deptid) VALUES ('tonny', '1');
INSERT INTO `tb_emp`(name,deptid) VALUES ('mary', '2');
INSERT INTO `tb_emp`(name,deptid) VALUES ('rose', '2');
INSERT INTO `tb_emp`(name,deptid) VALUES ('luffy', '3');
INSERT INTO `tb_emp`(name,deptid) VALUES ('outman', '14');
tb_dept表:
DROP TABLE IF EXISTS `tb_dept`;
CREATE TABLE `tb_dept` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`deptname` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `tb_dept`(deptname) VALUES ('研发');
INSERT INTO `tb_dept`(deptname) VALUES ('测试');
INSERT INTO `tb_dept`(deptname) VALUES ('运维');
INSERT INTO `tb_dept`(deptname) VALUES ('经理');
一、inner join
注:A表示tb_emp,B表示tb_dept,下同。
inner join:A、B共有,也就是交集。
二、left join
left jion:A独有+AB共有(交集)
三、right join
right join:B独有+AB共有(交集)
四、A独有
注:参照left join,A独有只是将AB交集部分去掉。
五、B独有
注:参照right join,B独有只是将AB交集部分去掉。
六、AB全有(并集)
由于mysql中不支持full outer join,所以这里通过union进行转换。AB并集:AB交集+A独有+B独有。
七、A、B独有并集
A、B独有并集,相当于A、B全有去掉AB的共有(交集)。