Mysql 多表查询详解

原创 2016年04月27日 16:33:48

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:TableB:

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

结果如下:

应用场景:

这种场景下得到的是满足某一条件的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:


应用场景:


这种场景下得到的是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

结果如下:


应用场景:

这种场景下得到的是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:

应用场景:


这种场景下得到的是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
结果如下:


应用场景:


这种场景下得到的是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的三条记录),所以结果如下:

应用场景:



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

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
结果如下:


应用场景:


这种场景下得到的是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


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对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: 鉴于之前排版不够美观,现本文已重新整理,以便更好为大家学习交流

版权声明:本文为博主原创文章,未经博主允许不得转载。

mysql查询语句 和 多表关联查询 以及 子查询

mysql(三):mysql查询语句 和 多表关联查询 以及 子查询 1.查询一张表:     select * from 表名; 2.查询指定字段...
  • github_37767025
  • github_37767025
  • 2017年03月28日 14:12
  • 8114

MySQL多表查询核心优化

在一般的项目开发中,对数据表的多表查询是必不可少的。而对于存在大量数据量的情况时(例如百万级数据量),我们就需要从数据库的各个方面来进行优化,本文就先从多表查询开始。...
  • u013761665
  • u013761665
  • 2016年03月22日 17:00
  • 5102

MySQL多表连接查询

连接查询是数据库查询中很重要的一个组成部分。而多表连接查询是在日常开发中经常需要用到的一个操作。在此对多表连接查询做一个总结,为了便于理解本文以一个实际开发中的案例作为例子来讲解。 表结构首先我们创建...
  • FX_SKY
  • FX_SKY
  • 2016年05月13日 20:02
  • 1104

mysql两表联合查询的四种情况

一般来说,我们为了得到更完整的结果,我们需要从两个或更多的表中获取结果,我一般都是用select xxx,xxx from 表1,表2 where 表1.xxx=表2.xxx,我们一般都是进行的是这般...
  • wj123446
  • wj123446
  • 2016年10月20日 10:55
  • 18079

MySQL多表联表查询

需求分析:现有一个参数需要根据这个参数(这个参数可能只有一个表才有)查询两个表的信息,但是你又不能查询两次数据库,此时就可以使用连表查询。 前提:必须有一个两个表有相同数值的字段。 select * ...
  • nowadaysall
  • nowadaysall
  • 2015年10月27日 18:43
  • 16923

对mysql多表查询的理解

多表查询就是在多个有逻辑联系的表之间进行的查询,逻辑关系主要是指主外键之间的联系, 在主表中的某个字段的值取自另一个表中的一个字段,就是说主表中的一个字段的值包含于另一个表的一个字段的值的集合里 。这...
  • u012723798
  • u012723798
  • 2015年08月01日 19:59
  • 476

MySQL联合查询语法内联、左联、右联、全联

MySQL联合查询效率较高,以下例子来说明联合查询(内联、左联、右联、全联)的好处: T1表结构(用户名,密码)    userid(int)   usernamevarchar(20) ...
  • YL_WH
  • YL_WH
  • 2013年03月08日 11:25
  • 93848

Mysql联表查询

一.内联结、外联结、左联结、右联结的含义及区别 在SQL标准中规划的(Join)联结大致分为下面四种: 1.内联结:将两个表中存在联结关系的字段符合联结关系的那些记录形成记录集的联结。 2.外联...
  • qmhball
  • qmhball
  • 2012年09月20日 14:23
  • 25922

mysql两表联合查询,一对多

t_user表如下 cj表如下: 要获得的查询结果: 解决办法: select cjc.uid,u.uname,cjc.数学,cjc.英语,cjc.语文,u.type from   (s...
  • superit401
  • superit401
  • 2017年03月09日 09:50
  • 4497

【explain】MySQL联表查询中的驱动表

写在前面 1、不要求每个人一定理解 联表查询(join/left join/inner join等)时的mysql运算过程 2、不要求每个人一定知道线上(现在或未来)哪张表数据量大,哪张表数据量小...
  • u013065023
  • u013065023
  • 2017年02月10日 10:42
  • 3295
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Mysql 多表查询详解
举报原因:
原因补充:

(最多只允许输入30个字)