Mysql的7种join_mysql中有没有bucketjoin

Mysql的7种join，各种项目中，数据库经常用到，也是必不可少的，数据库查询中经常使用到两个表之间的查询甚至是多表之间的查询，以下列举了mysql的7种join语句。

建表语句

建两个表，有外键相关联的表。

create table user
(
	id int primary key,
    name char(11)
)engine=innodb;
 
 
create table role
(
	rid int primary key,
    rname char(11),
    uid int,
    constraint foreign key (uid) references user(id)
)engine=innodb;
 
insert into user values(1,'xiaohai');
insert into user values(2,'xiaolan');
insert into user values(3,'xiaoxiao');
insert into user values(4,'xiaolong');
insert into user values(5,'xiaolei');
insert into role values(1,'admin',1);
insert into role values(2,'user',2);
insert into role values(3,'vip1',3);
insert into role values(4,'vip2',4);
insert into role(rid,rname) values(5,'admin');

1.文氏图左外连接

SQL语句

select u.*,r.* from user u left join role r on u.id = r.uid ;

查询结果：

2.文氏图右外连接

SQL语句

select u.*,r.* from user u right join role r on u.id = r.uid ;

查询结果

 

3.文氏图左连接

SQL语句:去掉表A表B都有的，单独显示表A的元素

select u.*,r.* from user u left join role r on u.id = r.uid where r.rid is null;

查询结果

 

4.文氏图右连接

SQL语句

select u.*,r.* from user u right join role r on u.id = r.uid  where u.id is null;

查询结果

 

5.文氏图内连接

sql语句

select u.*,r.* from user u inner join role r on u.id = r.uid;

查询结果

 

6.全连接：oracle数据库有full join ... on语句，mysql没有。

SQL语句

select u.*,r.* from user u left join role r on u.id = r.uid where r.rid is null
union
select u2.*,r2.* from user u2 right join role r2 on u2.id = r2.uid;

查询结果

 

7.两张表中都没有出现的数据集：就是两张表各自独立的那部分数据集，没有相同部分的数据集。

文氏图

SQL语句

select u.*,r.* from user u left join role r on u.id = r.uid where r.rid is null
union
select u2.*,r2.* from user u2 right join role r2 on u2.id = r2.uid  where u2.id is null;

查询结果