Mysql 多表查询详解

Mysql 多表查询详解纸上得来终觉浅1.前言:上篇讲到Mysql中关键字执行的顺序,只涉及了一张表;实际应用大部分情况下,查询语句都会涉及到多张表格:1)多表连接有哪些分类;2)针对这些分类有哪些连接方法?3)这些连接方法分别作用于哪些应用场景?这篇针对这三个点通过实例来讲述,目的是穷尽所有的场景和所有的方法,并且对每个方法的使用做实例。2.假设有两张表格A和B,把表格当作一个集合,那么表格中

大家好,又见面了,我是你们的朋友全栈君。

Mysql 多表查询详解

一.前言 

二.示例

三.注意事项

一.前言 

上篇讲到Mysql中关键字执行的顺序,只涉及了一张表;实际应用大部分情况下,查询语句都会涉及到多张表格 :

1.1 多表连接有哪些分类?

1.2 针对这些分类有哪些连接方法?

1.3 这些连接方法分别作用于哪些应用场景?

这篇针对这三个点通过实例来讲述,目的是穷尽所有的场景和所有的方法,并且对每个方法的使用做实例。

首先先列举本篇用到的分类(内连接,外连接,交叉连接)和连接方法(如下):

A)内连接join,inner join

B)外连接left join,left outer join,right join,right outer join,union

C)交叉连接cross join


二.下面以实例进行分析

两张假设有两张表格A和B,把表格当作一个集合,那么表格中的记录就是集合中的一个元素。

两张表格如下:

TableA:Mysql 多表查询详解TableB:Mysql 多表查询详解

2.1 内连接(只有一种场景)

inner join 或者join(等同于inner join)

select a.*, b.* from tablea a
inner join tableb b
on a.id = b.id

select a.*, b.* from tablea a
join tableb b
on a.id = b.id

结果如下:

Mysql 多表查询详解

应用场景:

Mysql 多表查询详解

这种场景下得到的是满足某一条件的A,B内部的数据;正因为得到的是内部共有数据,所以连接方式称为内连接。

2.2 外连接(六种场景)

2.2.1 left join 或者left outer join(等同于left join)

select a.*, b.* from tablea a
left join tableb b
on a.id = b.id

或者

select a.*, b.* from tablea a
left outer join tableb b
on a.id = b.id

结果如下,TableB中更不存在的记录填充Null:

Mysql 多表查询详解

应用场景:

Mysql 多表查询详解

这种场景下得到的是A的所有数据,和满足某一条件的B的数据;

2.2.2  [left   join 或者left outer join(等同于left join)]  +  [where B.column is null]

select a.id aid,a.age,b.id bid,b.name from tablea a
left join tableb b
on a.id = b.id
Where b.id is null

结果如下:

Mysql 多表查询详解

应用场景:

Mysql 多表查询详解

这种场景下得到的是A中的所有数据减去”与B满足同一条件 的数据”,然后得到的A剩余数据;

2.2.3  right join 或者fight outer join(等同于right join)

select a.id aid,a.age,b.id bid,b.name from tablea a
right join tableb b
on a.id = b.id

结果如下,TableB中更不存在的记录填充Null:


Mysql 多表查询详解

应用场景:

Mysql 多表查询详解

这种场景下得到的是B的所有数据,和满足某一条件的A的数据;

2.2.4 [left   join 或者left outer join(等同于left join)]  +  [where A.column is null]

select a.id aid,a.age,b.id bid,b.name from tablea a
right join tableb b
on a.id = b.id
where a.id is null

结果如下:

Mysql 多表查询详解
应用场景:

Mysql 多表查询详解

这种场景下得到的是B中的所有数据减去 “与A满足同一条件 的数据“,然后得到的B剩余数据;

2.2.5 full join (mysql不支持,但是可以用 left join  union right join代替)

select a.id aid,a.age,b.id bid,b.name from tablea a
left join tableb b
on a.id = b.id
union
select a.id aid,a.age,b.id bid,b.name from tablea a
right join tableb b
on a.id = b.id

union过后,重复的记录会合并(id为2,3,4的三条记录),所以结果如下:


Mysql 多表查询详解

应用场景:

Mysql 多表查询详解

这种场景下得到的是满足某一条件的公共记录,和独有的记录

2.2.6 full join + is null(mysql不支持,但是可以用 (left join + is null) union (right join+isnull代替)

select a.id aid,a.age,b.id bid,b.name from tablea a
left join tableb b
on a.id = b.id
where b.id is null
union
select a.id aid,a.age,b.id bid,b.name from tablea a
right join tableb b
on a.id = b.id
where a.id is null

结果如下:

Mysql 多表查询详解

应用场景:

Mysql 多表查询详解

这种场景下得到的是A,B中不满足某一条件的记录之和

注:上面共有其中七(2^3-1)种应用场景,还有一种是全空白,那就是什么都不查,七种情形包含了实际应用所有可能的场景

2.3 交叉连接 (cross join)

2.3.1 实际应用中还有这样一种情形,想得到A,B记录的排列组合,即笛卡儿积,这个就不好用集合和元素来表示了。需要用到cross join:

select a.id aid,a.age,b.id bid,b.name from tablea a
cross join tableb b

Mysql 多表查询详解

2.3.2 还可以为cross  join指定条件 (where):

select a.id aid,a.age,b.id bid,b.name from tablea a
cross join tableb b
where a.id = b.id

结果如下;

Mysql 多表查询详解

注:这种情况下实际上实现了内连接的效果

三 注意事项

上面仍然存在遗漏,那就是mysql对sql语句的容错问题,即在sql语句不完全符合书写建议的情况,mysql会允许这种情况,尽可能地解释它:

3.1 一般cross join后面加上where条件,但是用cross join+on也是被解释为cross join+where;

3.2 一般内连接都需要加上on限定条件,如上面场景2.1;如果不加会被解释为交叉连接;

3.3 如果连接表格使用的是逗号,会被解释为交叉连接;

注:sql标准中还有union join和natural  inner join,mysql不支持,而且本身也没有多大意义,其结果可以用上面的几种连接方式得到

总结:总结了mysql所有连接方法,其中有一些是之前没有注意到的问题,平时开发也都不外乎这些。

PS-1: 鉴于之前排版不够美观,现本文已重新整理,以便更好为大家学习交流

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/138155.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • 通过ProGet搭建一个内部的Nuget服务器

    通过ProGet搭建一个内部的Nuget服务器

  • python中输入输出

    python中输入输出一个程序要进行交互,就需要进行输入,进行输入→处理→输出的过程。所以就需要用到输入和输出功能。同样的,在Python中,怎么实现输入和输出?Python3中的输入方式:Python提供了&#160

  • (更新)2009.5.23 软考 网络管理员 上午试题 答案 讨论

    (更新)2009.5.23 软考 网络管理员 上午试题 答案 讨论

  • 决策树原理解析_解析的原理

    决策树原理解析_解析的原理决策树原理解析1.决策树算法以及基本流程决策树是基于树结构进行决策的,其机制就是通过判定每个属性分类的纯度来进行自上而下决策分类决策树包含根结点,内部结点,叶结点;根结点和内部结点对应与分类的属性(也就是分类的基准),叶结点对应决策结果(也就是纯度很高且不需要继续分裂的类别);从根结点到某一个叶结点的路径便是当前叶结点对应类的整个决策过程,下面来看决策树的算法流程:可以看到决…

  • RPC接口设计_java rpc项目

    RPC接口设计_java rpc项目写在前边分布式架构是互联网应用的基础架构,很多新人入职以来就开始负责编写和调用阿里的各种远程接口。但如同结婚一般,用对一个正确的接口就如同嫁一个正确的人一样,往往难以那么顺利的实现,或多或少大家都会在这个上边吃亏。每年双十一系统调用复盘的时候,我都会听到以下声音你们调我的接口报错了竟然不会自己重试?我的返回值应该从这里取我返回isSuccess()==true,不代表业务成功,你…

    2022年10月13日
  • IDEA开发工具使用 git 创建项目、拉取分支、合并分支「建议收藏」

    IDEA开发工具使用 git 创建项目、拉取分支、合并分支「建议收藏」工作中多人使用版本控制软件协作开发,常见的应用场景归纳如下:假设小组中有两个人,组长小张,组员小袁场景一:小张创建项目并提交到远程Git仓库场景二:小袁从远程Git仓库上获取项目源码场景三:小袁修改了部分源码,提交到远程仓库场景四:小张从远程仓库获取小袁的提交场景五:小袁接受了一个新功能的任务,创建了一个分支并在分支上开发场景六:小袁把分支提交到远程Git仓库场景七:小张获取小袁提交的分支场景八:…

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号