MySQL七种JOIN类型小结

在开始之前，我们创建两个表用于演示将要介绍的其中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;

初始化数据

七种join

1. a ∩ b

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

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

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

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

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

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

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

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

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

6. a ∪ b

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

7. a ∪ b – a ∩ b

 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类型内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！

MySQL七种JOIN类型小结

相关推荐