在开始之前，我们创建两个表用于演示将要介绍的其中join类型。
建表
|
1
2
3
4
5
6
|
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 default charset=utf8; |
|
1
2
3
4
5
6
7
|
create table `tbl_emp` ( `id` int(11) not null auto_increment, `name` varchar(20) default null, `deptid` varchar(11) not null, primary key (`id`), key `fk_dept_id` (`deptid`)) engine=innodb auto_increment=1 default charset=utf8; |
初始化数据
七种join
1. a ∩ b
|
1
2
3
4
|
select < select_list >from tablea ainner join tableb b # 共有on a.key = b.key |
2. a ( = a ∩ b + a* )
|
1
2
3
4
|
select < select_list >from tablea aleft join tableb bon a.key = b.key |
3. b ( = a ∩ b + b* )
|
1
2
3
4
|
select < select_list >from tablea aright join tableb bon a.key = b.key |
4. a* ( = a - a ∩ b )
|
1
2
3
4
5
|
select < select_list >from tablea aleft join tableb bon a.key = b.key # on时主表保留where b.key is null # 筛选a表数据 |
5. b* ( = b - a ∩ b )
|
1
2
3
4
5
|
select < select_list >from tablea aright join tableb bon a.key = b.keywhere a.key is null |
6. a ∪ b
|
1
2
3
4
|
select < select_list >from tablea afull outer join tableb b ## full outer 仅oracle支持on a.key = b.key |
7. a ∪ b - a ∩ b
|
1
2
3
4
5
|
select < select_list >from tablea afull outer join tableb bon a.key = b.keywhere a.key is null or b.key is null |
到此这篇关于mysql七种join类型详解的文章就介绍到这了,更多相关mysql join类型内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！
原文链接：https://blog.csdn.net/qq_51026595/article/details/120895365
