MySQL 7种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);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

1.内连接

select * from tbl_emp inner join tbl_dept on tbl_emp.deptId = tbl_dept.id;
1

2.左外连接

select * from tbl_emp left join tbl_dept on tbl_emp.deptId = tbl_dept.id;
1

3.右外连接

select * from tbl_emp right join tbl_dept on tbl_emp.deptId = tbl_dept.id;
1

4.A独有

select * from tbl_emp left join tbl_dept on tbl_emp.deptId = tbl_dept.id where tbl_dept.id is null;
1

5.B独有

select * from tbl_emp right join tbl_dept on tbl_emp.deptId = tbl_dept.id where tbl_emp.deptId is null;
1

6.全有

MySQL不支持全连接！！ 不能使用full outer join

使用union来完成，union作用：合并+去重

select * from tbl_emp left join tbl_dept on tbl_emp.deptId = tbl_dept.id
union
select * from tbl_emp right join tbl_dept on tbl_emp.deptId = tbl_dept.id;
1
2
3

7.A独有与B独有

select * from tbl_emp left join tbl_dept on tbl_emp.deptId = tbl_dept.id where tbl_dept.id is null
union
select * from tbl_emp right join tbl_dept on tbl_emp.deptId = tbl_dept.id where tbl_emp.deptId is null;
1
2
3