MYSQL连表查询

╰+哭是因爲堅強的太久メ 2022-05-29 12:46 277阅读 0赞

#                                         MYSQL连表查询 #

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

A）内连接：join，inner join

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

C）交叉连接：cross join

二.下面以实例进行分析

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

两张表格如下：

TableA：![Center][]TableB：![Center 1][]

2.1 内连接（只有一种场景）

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

\[java\]  [view plain][]  [copy][view plain]

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

或

\[java\]  [view plain][]  [copy][view plain]

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

结果如下：

![Center 2][]

应用场景:

![Center 3][]

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

2.2 外连接（六种场景）

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

\[java\]  [view plain][]  [copy][view plain]

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

或者

\[java\]  [view plain][]  [copy][view plain]

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的数据;

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

\[java\]  [view plain][]  [copy][view plain]

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剩余数据；

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

\[java\]  [view plain][]  [copy][view plain]

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的数据；

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

\[java\]  [view plain][]  [copy][view plain]

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剩余数据；

2.2.5 full join （mysql不支持，但是可以用 left join  union right join代替）

\[java\]  [view plain][]  [copy][view plain]

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][]

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

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

\[java\]  [view plain][]  [copy][view plain]

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）种应用场景，还有一种是全空白，那就是什么都不查，七种情形包含了实际应用所有可能的场景

2.3 交叉连接 （cross join）

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

\[java\]  [view plain][]  [copy][view plain]

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

![Center 16][]

2.3.2 还可以为cross  join指定条件 （where）：

\[java\]  [view plain][]  [copy][view plain]

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][]

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

三 注意事项

上面仍然存在遗漏，那就是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不支持，而且本身也没有多大意义，其结果可以用上面的几种连接方式得到

[Center]: https://img-blog.csdn.net/20160427142749563?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 1]: /images/20220529/e781d2eff04f4eee910ec4726d54dc76.png
[view plain]: https://blog.csdn.net/jintao_ma/article/details/51260458#
[Center 2]: /images/20220529/adb9bc8fd51a40329551c1a09257399b.png
[Center 3]: /images/20220529/1ddbb842dc534129bc491f10eb426b2c.png
[Center 4]: /images/20220529/aba99b916603451dbd7e4df1e37a8862.png
[Center 5]: /images/20220529/fbad5c1846ba427ebb733153ffab123f.png
[Center 6]: /images/20220529/f631c9d0d19e4880bae9807995ccd1b3.png
[Center 7]: /images/20220529/4bb9c26c3de84dc6ae5d6266583f015d.png
[Center 8]: /images/20220529/3278e3c9502744e4aa039310f8508dca.png
[Center 9]: /images/20220529/52ce388d99914514877d84396d673fb5.png
[Center 10]: /images/20220529/5b92cef5775c4823bb5e4d9509b726b7.png
[Center 11]: /images/20220529/9298984bd67049debef3d4f4257bce8c.png
[Center 12]: /images/20220529/7763151ff9c9401290de352de9f1da27.png
[Center 13]: /images/20220529/61e1991ed998471da716be27ed478399.png
[Center 14]: /images/20220529/2f12d5c59ac84b928f6a2ed1f61e2c5d.png
[Center 15]: /images/20220529/a6f01bb82028446494c682f997ba40c3.png
[Center 16]: /images/20220529/3b5544e7151c4e6f8d5b4358aa936bab.png
[Center 17]: /images/20220529/d5d8a8172dc24801b1878fef52147bce.png