欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL七种JOIN类型小结

程序员文章站 2022-06-19 15:06:14
在开始之前,我们创建两个表用于演示将要介绍的其中join类型。建表create table `tbl_dept` ( `id` int(11) not null auto_increment,...

        在开始之前,我们创建两个表用于演示将要介绍的其中join类型。

建表

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;
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;

初始化数据

MySQL七种JOIN类型小结MySQL七种JOIN类型小结

七种join

1. a ∩ b

MySQL七种JOIN类型小结

 select < select_list >
 from tablea a
 inner join tableb b # 共有
 on a.key = b.key

MySQL七种JOIN类型小结

2. a ( = a ∩ b + a* )

MySQL七种JOIN类型小结

 select < select_list >
 from tablea a
 left join tableb b
 on a.key = b.key

MySQL七种JOIN类型小结

3. b ( = a ∩ b + b* )

MySQL七种JOIN类型小结

 select < select_list >
 from tablea a
 right join tableb b
 on a.key = b.key

MySQL七种JOIN类型小结 

4. a* ( = a - a ∩ b )

 MySQL七种JOIN类型小结

 select < select_list >
 from tablea a
 left join tableb b
 on a.key = b.key # on时主表保留
 where b.key is null # 筛选a表数据

MySQL七种JOIN类型小结

5. b* ( = b - a ∩ b )

MySQL七种JOIN类型小结

 select < select_list >
 from tablea a
 right join tableb b
 on a.key = b.key
 where a.key is null

MySQL七种JOIN类型小结

6. a ∪ b

 MySQL七种JOIN类型小结

 select < select_list >
 from tablea a
 full outer join tableb b ## full outer 仅oracle支持
 on a.key = b.key

 MySQL七种JOIN类型小结

7. a ∪ b - a ∩ b

MySQL七种JOIN类型小结

 select < select_list >
 from tablea a
 full outer join tableb b
 on a.key = b.key
 where a.key is null or b.key is null

MySQL七种JOIN类型小结

到此这篇关于mysql七种join类型详解的文章就介绍到这了,更多相关mysql join类型内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持!

相关标签: mysql join 类型