mysql中各种常见join连表查询实例总结

本文实例讲述了mysql中各种常见join连表查询。分享给大家供大家参考，具体如下：

通常我们需要连接多个表查询数据，以获取想要的结果。

一、连接可以分为三类：

　　(1) 内连接：join，inner join

　　(2) 外连接：left join，left outer join，right join，right outer join，union，union all

　　(3) 交叉连接：cross join

 

二、准备需要演示的表：

?

1 2 3 4 5 6 7 8 9 10 11 12

create table `a` (   `id` int (11) unsigned not null auto_increment comment 'id' ,   `a_name` varchar (32) default '' comment 'a表名称' ,   primary key (`id`) ) engine=innodb default charset=utf8mb4;   create table `b` (   `id` int (11) unsigned not null auto_increment comment 'id' ,   `a_id` int (11) default '0' comment 'a表id' ,   `b_name` varchar (32) default '' comment 'b表名称' ,   primary key (`id`) ) engine=innodb default charset=utf8mb4;

a表与b表的数据如图中所示：

三、内连接inner join或join

?

1 2 3

select * from a inner join b on a.id = b.a_id; select * from a join b on a.id = b.a_id; select * from a, b where a.id = b.a_id;

结果如下：

内连接可以理解为，两个表中同时满足某条件的数据记录组合。也就是表a和表b中满足条件a.id = b.a_id的所有记录。

当表a中的一条记录对应表b中的多条记录时，会以重复的方式对应多条表b记录出现在结果集中。

当表b中的一条记录对应表a中的多条记录时，会以重复的方式对应多条表a记录出现在结果集中。

四、外连接left join或right join

　　(1) 左外连接

?

1 2	select * from a left join b on a.id = b.a_id; select * from a left outer join b on a.id = b.a_id;

左外连接，会以左边的表a为主表，返回所有行，即使右表b中没有匹配的行。

如果左边的表a在右表b中找不到一条记录，则返回表a所有记录并且表b相应的字段设为null。

如果左边的表a在右表b中找到多条记录，则以相同表a记录和不同表b记录多条显示在结果集中。

这种情况下，其实是把表a中所有记录都查询出来了，包括不满足条件的记录。

如果我们只想查出表a中满足条件的，或是不满足条件的，该怎么查？

?

1 2	select * from a left join b on a.id = b.a_id where b.a_id is not null ; select * from a left outer join b on a.id = b.a_id where b.a_id is not null ;

上面的语句查询的，就是表a中满足条件的。

?

1 2	select * from a left join b on a.id = b.a_id where b.a_id is null ; select * from a left outer join b on a.id = b.a_id where b.a_id is null ;

上面的语句查询的，就是表a中不满足条件的。

　　(2) 右外连接

?

1 2	select * from a right join b on a.id = b.a_id; select * from a right outer join b on a.id = b.a_id;

右外连接其实跟左外连接一样，区别在于 主表的确定，两者之间可以相互转换。

右外连接的描述基本与左外连接相同，这里就不过多描述了。

　　(3) 全连接full join

mysql并不支持全连接，不过有相应的替代方案，就是left join union right join 来代替。

?

1 2 3

select * from a left join b on a.id = b.a_id union select * from a right join b on a.id = b.a_id;

全连接会从表a和表b中返回所有的行，如果表a中的行在表b中没有匹配，或是表b中的行在表a中没有匹配，这些行都会显示，不存在的字段以null补充。

union会把其中重复的行合并。

这种情况下，是把表a和表b中满足条件和不满足条件的记录都显示出来了。

如果只想显示所有不满足条件的记录，则通过如下语句：

?

1 2 3

select * from a left join b on a.id = b.a_id where b.a_id is null union select * from a right join b on a.id = b.a_id where a.id is null ;

如果只想显示所有满足条件的记录，则通过如下语句：

?

1 2 3

select * from a left join b on a.id = b.a_id where b.a_id is not null union select * from a right join b on a.id = b.a_id where a.id is not null ;

 五、交叉连接

交叉连接实际上就是表a与表b的笛卡尔乘积。

?

1 2	select * from a cross join b; select * from a, b;

希望本文所述对大家MySQL数据库计有所帮助。

原文链接：https://www.cnblogs.com/jkko123/p/10129154.html