热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

MySQL多表联合查询技术详解

本文详细解析了MySQL中的多表联合查询技术,涵盖了内连接、外连接和交叉连接三种主要类型。通过具体示例,深入探讨了每种连接方式的使用场景和实现方法,帮助读者全面掌握SQL查询技巧,提升数据处理能力。

                                        MYSQL连表查询

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

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)

[java] view plain copy
  1. select a.*, b.* from tablea a  
  2. inner join tableb b  
  3. on a.id = b.id  

[java] view plain copy
  1. select a.*, b.* from tablea a  
  2. join tableb b  
  3. on a.id = b.id  

结果如下:

应用场景:

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

2.2 外连接(六种场景)

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

[java] view plain copy
  1. select a.*, b.* from tablea a  
  2. left join tableb b  
  3. on a.id = b.id  

或者

[java] view plain copy
  1. select a.*, b.* from tablea a  
  2. left outer join tableb b  
  3. on a.id = b.id  

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


应用场景:


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

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

[java] view plain copy
  1. select a.id aid,a.age,b.id bid,b.name from tablea a  
  2. left join tableb b  
  3. on a.id = b.id  
  4. Where b.id is null  

结果如下:


应用场景:

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

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

[java] view plain copy
  1. select a.id aid,a.age,b.id bid,b.name from tablea a  
  2. right join tableb b  
  3. on a.id = b.id  
结果如下,TableB中更不存在的记录填充Null:

应用场景:


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

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

[java] view plain copy
  1. select a.id aid,a.age,b.id bid,b.name from tablea a  
  2. right join tableb b  
  3. on a.id = b.id  
  4. where a.id is null  
结果如下:


应用场景:


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

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

[java] view plain copy
  1. select a.id aid,a.age,b.id bid,b.name from tablea a  
  2. left join tableb b  
  3. on a.id = b.id  
  4. union  
  5. select a.id aid,a.age,b.id bid,b.name from tablea a  
  6. right join tableb b  
  7. 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代替)

[java] view plain copy
  1. select a.id aid,a.age,b.id bid,b.name from tablea a  
  2. left join tableb b  
  3. on a.id = b.id  
  4. where b.id is null  
  5. union  
  6. select a.id aid,a.age,b.id bid,b.name from tablea a  
  7. right join tableb b  
  8. on a.id = b.id  
  9. where a.id is null  
结果如下:


应用场景:


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

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

2.3 交叉连接 (cross join)

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

[java] view plain copy
  1. select a.id aid,a.age,b.id bid,b.name from tablea a  
  2. cross join tableb b  


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

[java] view plain copy
  1. select a.id aid,a.age,b.id bid,b.name from tablea a  
  2. cross join tableb b  
  3. 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不支持,而且本身也没有多大意义,其结果可以用上面的几种连接方式得到




推荐阅读
author-avatar
低调vs完美
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有