Mysql 多表查询详解

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

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

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

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

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

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

内链接：join，inner join

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

交叉连接：cross join

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

两张表格如下：

TableA：TableB：

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

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

[html]  view plain  copy

 

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

或

[html]  view plain  copy

 

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

结果如下：

应用场景：

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

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

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

[html]  view plain  copy

 

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

或者

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

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

[html]  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剩余数据；
C）right join 或者fight outer join(等同于right join)

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

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

[html]  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剩余数据；
E）full join （mysql不支持，但是可以用 left join  union right join代替）

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

应用场景：

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

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

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

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

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

[html]  view plain  copy

 

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

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

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

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

结果如下：

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

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

总结：总结了mysql所有连接方法，其中有一些是之前没有注意到的问题，平时开发也都不外乎这些。下篇总结写sql提供的函数