原文链接
Mysql 多表查询详解
一.前言
二.示例
三.注意事项
一.前言
上篇讲到Mysql中关键字执行的顺序,只涉及了一张表;实际应用大部分情况下,查询语句都会涉及到多张表格 :
1.1 多表连接有哪些分类?
1.2 针对这些分类有哪些连接方法?
1.3 这些连接方法分别作用于哪些应用场景?
这篇针对这三个点通过实例来讲述,目的是穷尽所有的场景和所有的方法,并且对每个方法的使用做实例。
首先先列举本篇用到的分类(内连接,外连接,交叉连接)和连接方法(如下):
A)内连接:join,inner join
B)外连接:left join,left outer join,right join,right outer join,union
C)交叉连接:cross join
二.下面以实例进行分析
两张假设有两张表格A和B,把表格当作一个集合,那么表格中的记录就是集合中的一个元素。
两张表格如下:
TableA:
+—-+——–+
| id | firmid |
+—-+——–+
| 2 | 10002 |
| 3 | 10003 |
| 4 | 10004 |
| 5 | 10005 |
| 6 | 10006 |
+—-+——–+
TableB:
+—-+———+
| id | balance |
+—-+———+
| 1 | 100.10 |
| 2 | 899.20 |
| 3 | 98.20 |
| 4 | 11.20 |
+—-+———+
2.1 内连接(只有一种场景)
inner join 或者join(等同于inner join)
SELECT
a.*, b.*
FROM
firmid a
JOIN balance b ON a.id = b.id
或
SELECT
a.*, b.*
FROM
firmid a
inner JOIN balance b ON a.id = b.id
结果如下:
+—-+——–+—-+———+
| id | firmid | id | balance |
+—-+——–+—-+———+
| 2 | 10002 | 2 | 899.20 |
| 3 | 10003 | 3 | 98.20 |
| 4 | 10004 | 4 | 11.20 |
+—-+——–+—-+———+
应用场景:
这种场景下得到的是同时满足某一条件的A,B内部的数据;正因为得到的是内部共有数据,所以连接方式称为内连接。
一般用于查询多表共同的数据,查找交集。
2.2 外连接(六种场景)
2.2.1 left join 或者left outer join(等同于left join)
SELECT
a.*, b.*
FROM
firmid a
left JOIN balance b ON a.id = b.id
或者
SELECT
a.*, b.*
FROM
firmid a
left outer join balance b ON a.id = b.id
结果如下,TableB中更不存在的记录填充Null:
+—-+——–+——+———+
| id | firmid | id | balance |
+—-+——–+——+———+
| 2 | 10002 | 2 | 899.20 |
| 3 | 10003 | 3 | 98.20 |
| 4 | 10004 | 4 | 11.20 |
| 5 | 10005 | NULL | NULL |
| 6 | 10006 | NULL | NULL |
+—-+——–+——+———+
应用场景:
这种场景下得到的是A的所有数据,和满足某一条件的B的数据;
一般用于多表查询,主表A中关联B表的数据连同A表数据一起显示。
2.2.2 [left join 或者left outer join(等同于left join)] + [where B.column is null]
SELECT
a.*, b.*
FROM
firmid a
left join balance b ON a.id = b.id
where b.id is null
结果如下:
+—-+——–+——+———+
| id | firmid | id | balance |
+—-+——–+——+———+
| 5 | 10005 | NULL | NULL |
| 6 | 10006 | NULL | NULL |
+—-+——–+——+———+
应用场景:
这种场景下得到的是A中的所有数据减去”与B满足同一条件 的数据”,然后得到的A剩余数据;
A存在B不存在的数据。
2.2.3 right join 或者right outer join(等同于right join)
SELECT
a.*, b.*
FROM
firmid a
right join balance b ON a.id = b.id
结果如下,TableB中更不存在的记录填充Null:
+——+——–+—-+———+
| id | firmid | id | balance |
+——+——–+—-+———+
| 2 | 10002 | 2 | 899.20 |
| 3 | 10003 | 3 | 98.20 |
| 4 | 10004 | 4 | 11.20 |
| NULL | NULL | 1 | 100.10 |
+——+——–+—-+———+
应用场景:
这种场景下得到的是B的所有数据,和满足某一条件的A的数据;
B为主表,一般用于多表查询,主表B中关联A表的数据连同B表数据一起显示。
2.2.4 [RIGHT join 或者left outer join(等同于left join)] + [where A.column is null]
SELECT
a.*, b.*
FROM
firmid a
right join balance b ON a.id = b.id
where a.id is null
结果如下:
+——+——–+—-+———+
| id | firmid | id | balance |
+——+——–+—-+———+
| NULL | NULL | 1 | 100.10 |
+——+——–+—-+———+
应用场景:
这种场景下得到的是B中的所有数据减去 “与A满足同一条件 的数据“,然后得到的B剩余数据;
2.2.5 full join (mysql不支持,但是可以用 left join union right join代替)
SELECT
a.*, b.*
FROM
firmid a
left join balance b ON a.id = b.id
union
SELECT
a.*, b.*
FROM
firmid a
right join balance b ON a.id = b.id
union过后,重复的记录会合并(id为2,3,4的三条记录),所以结果如下:
+——+——–+——+———+
| id | firmid | id | balance |
+——+——–+——+———+
| 2 | 10002 | 2 | 899.20 |
| 3 | 10003 | 3 | 98.20 |
| 4 | 10004 | 4 | 11.20 |
| 5 | 10005 | NULL | NULL |
| 6 | 10006 | NULL | NULL |
| NULL | NULL | 1 | 100.10 |
+——+——–+——+———+
应用场景:
这种场景下得到的是满足某一条件的公共记录,和独有的记录
2.2.6 full join + is null(mysql不支持,但是可以用 (left join + is null) union (right join+isnull代替)
SELECT
a.*, b.*
FROM
firmid a
left join balance b ON a.id = b.id
where b.id is null
union
SELECT
a.*, b.*
FROM
firmid a
right join balance b ON a.id = b.id
where a.id is null
结果如下:
+——+——–+——+———+
| id | firmid | id | balance |
+——+——–+——+———+
| 5 | 10005 | NULL | NULL |
| 6 | 10006 | NULL | NULL |
| NULL | NULL | 1 | 100.10 |
+——+——–+——+———+
应用场景:
这种场景下得到的是A,B中不满足某一条件的记录之和
注:上面共有其中七(2^3-1)种应用场景,还有一种是全空白,那就是什么都不查,七种情形包含了实际应用所有可能的场景
2.3 交叉连接 (cross join)
2.3.1 实际应用中还有这样一种情形,想得到A,B记录的排列组合,即笛卡儿积,这个就不好用集合和元素来表示了。需要用到cross join:
SELECT
a.*, b.*
FROM
firmid a
cross join balance b
结果:
+—-+——–+—-+———+
| id | firmid | id | balance |
+—-+——–+—-+———+
| 2 | 10002 | 1 | 100.10 |
| 3 | 10003 | 1 | 100.10 |
| 4 | 10004 | 1 | 100.10 |
| 5 | 10005 | 1 | 100.10 |
| 6 | 10006 | 1 | 100.10 |
| 2 | 10002 | 2 | 899.20 |
| 3 | 10003 | 2 | 899.20 |
| 4 | 10004 | 2 | 899.20 |
| 5 | 10005 | 2 | 899.20 |
| 6 | 10006 | 2 | 899.20 |
| 2 | 10002 | 3 | 98.20 |
| 3 | 10003 | 3 | 98.20 |
| 4 | 10004 | 3 | 98.20 |
| 5 | 10005 | 3 | 98.20 |
| 6 | 10006 | 3 | 98.20 |
| 2 | 10002 | 4 | 11.20 |
| 3 | 10003 | 4 | 11.20 |
| 4 | 10004 | 4 | 11.20 |
| 5 | 10005 | 4 | 11.20 |
| 6 | 10006 | 4 | 11.20 |
2.3.2 还可以为cross join指定条件 (where):
SELECT
a.*, b.*
FROM
firmid a
cross join balance b
where a.id=b.id
结果如下;
+—-+——–+—-+———+
| id | firmid | id | balance |
+—-+——–+—-+———+
| 2 | 10002 | 2 | 899.20 |
| 3 | 10003 | 3 | 98.20 |
| 4 | 10004 | 4 | 11.20 |
+—-+——–+—-+———+
注:这种情况下实际上实现了内连接的效果
三 注意事项
上面仍然存在遗漏,那就是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不支持,而且本身也没有多大意义,其结果可以用上面的几种连接方式得到
总结:总结了mysql所有连接方法,其中有一些是之前没有注意到的问题,平时开发也都不外乎这些。