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;
查询结果