Mysql多表查询详解
1、多表连接分类：
-
A）内连接：join，inner join
-
B）外连接：left join，left outer join，right join，right outer join，union
-
C）交叉连接：cross join
2、数据表
两张表格如下：
TableA：
| id | age |
|---|---|
| 1 | 10 |
| 2 | 11 |
| 3 | 12 |
| 4 | 13 |
TableB：
| id | age |
|---|---|
| 2 | zhang |
| 3 | li |
| 4 | zhou |
| 5 | chen |
3、内连接（inner join 或者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
结果如下：
| id | age | id | age |
|---|---|---|---|
| 2 | 11 | 2 | zhang |
| 3 | 12 | 3 | li |
| 4 | 13 | 4 | zhou |
4、外连接（六种场景）
4.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:
| id | age | id | age |
|---|---|---|---|
| 1 | 10 | Null | Null |
| 2 | 11 | 2 | zhang |
| 3 | 12 | 3 | li |
| 4 | 13 | 4 | zhou |
4.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
结果如下:
| id | age | id | age |
|---|---|---|---|
| 1 | 10 | Null | Null |
4.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:
| id | age | id | age |
|---|---|---|---|
| 2 | 11 | 2 | zhang |
| 3 | 12 | 3 | li |
| 4 | 13 | 4 | zhou |
| Null | Null | 5 | chen |
4.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
结果如下:
| id | age | id | age |
|---|---|---|---|
| Null | Null | 5 | chen |
4.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的三条记录），所以结果如下：
| id | age | id | age |
|---|---|---|---|
| 1 | 10 | Null | Null |
| 2 | 11 | 2 | zhang |
| 3 | 12 | 3 | li |
| 4 | 13 | 4 | zhou |
| Null | Null | 5 | chen |
4.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
结果如下:
| id | age | id | age |
|---|---|---|---|
| 1 | 10 | Null | Null |
| Null | Null | 5 | chen |
5、交叉连接 （cross join）
5.1 cross join：
实际应用中还有这样一种情形，想得到排列组合，即笛卡儿积，这个就不好用集合和元素来表示了。需要用到cross join
select a.id aid,a.age,b.id bid,b.name from tablea a
cross join tableb b
结果如下:
| id | age | id | age |
|---|---|---|---|
| 1 | 10 | 2 | zhang |
| 2 | 11 | 2 | zhang |
| 3 | 12 | 2 | zhang |
| 4 | 13 | 2 | zhang |
| 1 | 10 | 3 | li |
| 2 | 11 | 3 | li |
| 3 | 12 | 3 | li |
| 4 | 13 | 3 | li |
| 1 | 10 | 4 | zhou |
| 2 | 11 | 4 | zhou |
| 3 | 12 | 4 | zhou |
| 4 | 13 | 4 | zhou |
| 1 | 10 | 5 | chen |
| 2 | 11 | 5 | chen |
| 3 | 12 | 5 | chen |
| 4 | 13 | 5 | chen |
5.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
结果如下;
| id | age | id | age |
|---|---|---|---|
| 2 | 11 | 2 | zhang |
| 3 | 12 | 3 | li |
| 4 | 13 | 4 | zhou |
注:这种情况下实际上实现了内连接的效果
6、总结
注意事项：
上面仍然存在遗漏，那就是mysql对sql语句的容错问题，即在sql语句不完全符合书写建议的情况，mysql会允许这种情况，尽可能地解释它:
-
一般cross join后面加上where条件，但是用cross join+on也是被解释为cross join+where；
-
一般内连接都需要加上on限定条件，如上面场景2.1；如果不加会被解释为交叉连接；
-
如果连接表格使用的是逗号，会被解释为交叉连接；