尚硅谷 MySQL高级课程 Join

7种Join

创建数据库
CREATE SCHEMA `sggtest` ;

创建部门表
create table `sggtest`.`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 default char set=utf8;

drop table if exists `sggtest`.`tbl_emp`;
create table `sggtest`.`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`),
constraint `fk_dept_id` foreign key (`deptId`) REFERENCES `tbl_dept`(`id`)
)auto_increment=1 default char set=utf8

外键约束 如果填入主表的外键在从表中没有，插入会出错。
比如现在tbl_emp表中，添加了外键约束，constraint没有隐掉，如果插入(‘s9’,51)，MySQL会报错。

如果表A的主关键字是表B中的字段，则该字段称为表B的外键，表A称为主表，表B称为从表。

插入数据

insert into `sggtest`.tbl_dept(deptName, locAdd) values('RD',11),
('HR',12),
('MK',13),
('MIS',14),
('FD',15);

无外键

insert into `sggtest`.`tbl_emp`(name, deptId) values('z3',1),
('z3',1),
('z4',1),
('z5',1),
('w5',2),
('w6',2),
('s7',3),
('s8',4),
('s9',51);

tbl_dept

1   RD  11
2   HR  12
3   MK  13
4   MIS 14
5   FD  15

tbl_emp

1   z3  1
2   z4  1
3   z5  1
4   w5  2
5   w6  2
6   s7  3
7   s8  4
8   s9  51

sql解析

删除表中所有数据，保留表的定义。内容没了，数据结构还在。

truncate table `sggtest`.`tbl_emp`;

笛卡尔积 相当于内连接 inner join 下面两行SQL查询语句结果相同

select * from tbl_emp, tbl_dept;
select * from tbl_emp inner join tbl_dept; 

结果

1   z3  1   1   RD  11
1   z3  1   2   HR  12
1   z3  1   3   MK  13
1   z3  1   4   MIS 14
1   z3  1   5   FD  15
2   z4  1   1   RD  11
2   z4  1   2   HR  12
2   z4  1   3   MK  13
2   z4  1   4   MIS 14
2   z4  1   5   FD  15
3   z5  1   1   RD  11
3   z5  1   2   HR  12
3   z5  1   3   MK  13
3   z5  1   4   MIS 14
3   z5  1   5   FD  15
4   w5  2   1   RD  11
4   w5  2   2   HR  12
4   w5  2   3   MK  13
4   w5  2   4   MIS 14
4   w5  2   5   FD  15
5   w6  2   1   RD  11
5   w6  2   2   HR  12
5   w6  2   3   MK  13
5   w6  2   4   MIS 14
5   w6  2   5   FD  15
6   s7  3   1   RD  11
6   s7  3   2   HR  12
6   s7  3   3   MK  13
6   s7  3   4   MIS 14
6   s7  3   5   FD  15
7   s8  4   1   RD  11
7   s8  4   2   HR  12
7   s8  4   3   MK  13
7   s8  4   4   MIS 14
7   s8  4   5   FD  15
8   s9  51  1   RD  11
8   s9  51  2   HR  12
8   s9  51  3   MK  13
8   s9  51  4   MIS 14
8   s9  51  5   FD  15

A和B的公有部分
内连接 下面的两个SQL语句行数是一样的，只有哪个表的列在前的区别

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

结果如下

1   z3  1   1   RD  11
2   z4  1   1   RD  11
3   z5  1   1   RD  11
4   w5  2   2   HR  12
5   w6  2   2   HR  12
6   s7  3   3   MK  13
7   s8  4   4   MIS 14

1   RD  11  1   z3  1
1   RD  11  2   z4  1
1   RD  11  3   z5  1
2   HR  12  4   w5  2
2   HR  12  5   w6  2
3   MK  13  6   s7  3
4   MIS 14  7   s8  4

理解起来就是在笛卡尔积中有40个结果，内连接就是找出笛卡尔积中满足on后面的语句的列。

SQL JOINS

左连接 A B公有 + A独有部分

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

1   z3  1   1   RD  11
2   z4  1   1   RD  11
3   z5  1   1   RD  11
4   w5  2   2   HR  12
5   w6  2   2   HR  12
6   s7  3   3   MK  13
7   s8  4   4   MIS 14
8   s9  51  null null null //此处是A表独有，B表没有，取null

左连接 A B公有部分 + B独有部分

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

这个是反的。第一列和最后一列是一样的，相当于右连接。不要用这种方法，默认A在前面，B在后面，相当于图中A在左边，B在右边。好一一对照。

1   RD  11  1   z3  1
1   RD  11  2   z4  1
1   RD  11  3   z5  1
2   HR  12  4   w5  2
2   HR  12  5   w6  2
3   MK  13  6   s7  3
4   MIS 14  7   s8  4
5   FD  15  null null null      

右连接 A和B公共部分 + B独有

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

1   z3  1   1   RD  11
2   z4  1   1   RD  11
3   z5  1   1   RD  11
4   w5  2   2   HR  12
5   w6  2   2   HR  12
6   s7  3   3   MK  13
7   s8  4   4   MIS 14
            5   FD  15

左连接 + wher b.id = null条件，可以得到A部分扣掉A，B公有部分。

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

8   s9  51  null null null      

右连接 + where a.depId is null

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

null null  null     5   FD  15

A+B全有

select * from tbl_emp a full outer join tbl_dept b on a.deptId = b.id;

出错 MySQL不支持full outer join 语法

MySQL需要相加 A左连接 + 右连接 + union
union自带去重操作

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;

1   z3  1   1   RD  11
2   z4  1   1   RD  11
3   z5  1   1   RD  11
4   w5  2   2   HR  12
5   w6  2   2   HR  12
6   s7  3   3   MK  13
7   s8  4   4   MIS 14
8   s9  51  null null null      
null null null  5   FD  15

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;

8   s9  51          
            5   FD  15