Mysql 多表查询详解

向右看齐 2022-06-17 08:53 181阅读 0赞

1)多表连接有哪些分类；

2)针对这些分类有哪些连接方法？

3)这些连接方法分别作用于哪些应用场景？

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

首先先列举本篇用到的连接方法：

内链接：join，inner join

外连接：left join，left outer join，right join，right outer join，union

交叉连接：cross join

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

两张表格如下：

ableA： ![Center][]

TableB：![Center 1][]

**1)分类**：内连接（只有一种场景）

inner join 或者join（等同于inner join）

**\[html\]** [ view plain][view plain] [ copy][view plain]  [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

1.  select a.\*, b.\* from tablea a
2.  inner join tableb b
3.  on a.id = b.id

或

**\[html\]** [ view plain][view plain] [ copy][view plain]  [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

1.  select a.\*, b.\* from tablea a
2.  join tableb b
3.  on a.id = b.id

结果如下：

![Center 2][]

应用场景：

![Center 3][]

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

**2)分类**：外连接（六种场景）

**A）**left join 或者left outer join(等同于left join)

**\[html\]** [ view plain][view plain] [ copy][view plain]  [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

1.  select a.\*, b.\* from tablea a
2.  left join tableb b
3.  on a.id = b.id

或者

**\[html\]** [ view plain][view plain] [ copy][view plain]  [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

1.  select a.\*, b.\* from tablea a
2.  left outer join tableb b
3.  on a.id = b.id

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

![Center 4][]

应用场景：

![Center 5][]

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

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

**\[html\]** [ view plain][view plain] [ copy][view plain]  [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

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

结果如下：

![Center 6][]

应用场景：

![Center 7][]

这种场景下得到的是A中的所有数据减去 和B满足同一条件 的数据，然后得到的A剩余数据；  
**C）**right join 或者fight outer join(等同于right join)

**\[html\]** [ view plain][view plain] [ copy][view plain]  [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

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：

![Center 8][]

应用场景：

![Center 9][]

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

**D）**\[left join 或者left outer join(等同于left join)\] + \[where A.column is null\]

**\[html\]** [ view plain][view plain] [ copy][view plain]  [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

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

结果如下：

![Center 10][]

应用场景：

![Center 11][]

这种场景下得到的是B中的所有数据减去 和A满足同一条件 的数据，然后得到的B剩余数据；  
**E）**full join （mysql不支持，但是可以用 left join union right join代替）

**\[html\]** [ view plain][view plain] [ copy][view plain]  [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

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的三条记录），所以结果如下：

![Center 12][]

应用场景：

![Center 13][]

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

**F）**full join + is null（mysql不支持，但是可以用 （left join + is null） union （right join+is null）代替）

**\[html\]** [ view plain][view plain] [ copy][view plain]  [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

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

结果如下：

![Center 14][]

应用场景：

![Center 15][]

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

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

**3）分类**：交叉连接 （cross join）

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

**\[html\]** [ view plain][view plain] [ copy][view plain]  [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

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

是A记录数\*B记录数，结果如下：

![Center 16][]  
**B）**还可以为cross join指定条件 （where）：

**\[html\]** [ view plain][view plain] [ copy][view plain]  [![在CODE上查看代码片][CODE]][CODE_CODE] [![派生到我的代码片][ico_fork.svg]][ico_fork.svg 1]

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

结果如下：

![Center 17][]  
注： 这种情况下实际上实现了内连接的效果

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

**1)**一般cross join后面加上where条件，但是用cross join+on也是被解释为cross join+where；

**2)**一般内连接都需要加上on限定条件，如上面场景一；如果不加会被解释为交叉连接；

**3)**如果连接表格使用的是逗号，会被解释为交叉连接；

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

[Center]: /images/20220617/04509b9be78d400b872f2bdb15253f02.png
[Center 1]: /images/20220617/eb31a3eae6c24a7eb4b7f57c7f001edc.png
[view plain]: http://blog.csdn.net/jintao_ma/article/details/51260458#
[CODE]: https://code.csdn.net/assets/CODE_ico.png
[CODE_CODE]: https://code.csdn.net/snippets/1664012
[ico_fork.svg]: https://code.csdn.net/assets/ico_fork.svg
[ico_fork.svg 1]: https://code.csdn.net/snippets/1664012/fork
[Center 2]: /images/20220617/d808b065a9ca4547a2fd9cb7b96bf549.png
[Center 3]: /images/20220617/a39a34b4d733479daa89b705f672a0d3.png
[Center 4]: /images/20220617/b9973050d320443bacb18a2c54dc90c9.png
[Center 5]: /images/20220617/3f9c19fb9d304bc4bb6682cee661ce68.png
[Center 6]: /images/20220617/a611bd8d96e84db6a669fef3b557b696.png
[Center 7]: /images/20220617/3a8f22dd19984039bff8a793180749f1.png
[Center 8]: /images/20220617/e1b8cb11fa924bf6942749486a64fd80.png
[Center 9]: /images/20220617/3db0338ac87f4021a962de3cff28ea68.png
[Center 10]: /images/20220617/8ec0992a9bb94ed3ac565c1be0d91292.png
[Center 11]: /images/20220617/d9209151a51046f8a3c824f84561c48a.png
[Center 12]: /images/20220617/5429abf625e446f599ff72b4e19cdcfa.png
[Center 13]: /images/20220617/3b075801b06e49c8960d67d6314d3a49.png
[Center 14]: /images/20220617/c897f130b8ab4865bfbd67fe7283ba72.png
[Center 15]: /images/20220617/379bed9dbbb2493c912990e78b50e053.png
[Center 16]: /images/20220617/d835fc31cb154037bbd9d812bf293227.png
[Center 17]: /images/20220617/b45009c93db14f10a41bdb7dce1a8567.png