多表关联——内连接和外连接

多表关联

场景：面对不同表里面的不同字段（从不同表里取不同字段合并起来）

补充知识：理解多表关联原理——笛卡尔积

• 利用多表关联我们可以从不同的表取需要的字段
• 在实际中，我们需要分析好业务逻辑，明确取数的规则或者逻辑后再进行查询
• 要考虑不同字段分析可以从哪些表取—有的字段可以从多张表取，有的字段只能从某张表取

内关联

• 内关联是针对列进行操作的

• 表与表之间需要通过相同意思的字段关联才有意义

join on 语句

select A.字段1，B.字段2，C.字段3

from 表A

(inner)join 表B  on  表A.字段=表B.字段

(inner)join 表C  on  表A.字段=表B.字段

……

where.条件

group by 汇总

• 可以把select   from   jion on 看作一张表
• where.条件 可以针对上述所有表

用以下列图片来对 jion on 语句进行说明

把订单明细表和订单表通过 order_id 和 id 关联起来 

Select t1.order_id , t2.id

From order_item t1

Join order t2 on t1.order_id = t2.id;

注意事项：

1. 在多表关联的时候：select 后面的列一定是表名.字段（避免关联的相关表有名字相同的字段）
2. 在多表关联中，必须给每个表取别名，这样就更方便
3. 多表关联时，必须用相同意义的字段，但并不代表字段的名称是一样的
4. 表中的字段名一样，并不意味着两个字段表示的意思也是一样的

使用案例及常见问题 

简单运用：从订单表中查和与订单明细表共有的id记录

• 先对比，从订单表中查询所有id

• 利用多表关联查询订单表中和订单明细表中相同的id

• 同时查询两表中共有的id

-- 1、先对比，从订单表中查询所有id
select id 
from db_order.sn_order_20210412;
-- 2、利用多表关联查询订单表中和订单明细表中相同的id
select t1.id 
from sn_order_20210412 t1
join sn_orderitem_20210412 t2 on t2.order_id = t1.id;
-- 3、同时查询两表中共有的id
select t1.id,t2.order_id
from sn_order_20210412 t1
join sn_orderitem_20210412 t2 on t2.order_id = t1.id;

 实际案例：统计会员购买的金额，购买的订单数，购买的商品数

• 先分别查看两表

• 分析：

1. 购买金额：order表

2. 购买订单数：order表/orderitem表

3. 购买商品数：orderitem表

-- 先分别查看两表
select *
from db_order.sn_order_20210412
limit 2;
select *
from db_order.sn_orderitem_20210412
limit 2;
-- 关联两表
select t1.member_id 
      ,sum(t1.order_money) as money
      ,count(t1.id) as order_num1
      ,count(t2.order_id) as order_num2 
      ,count(distinct t1.id) as order_num3
      ,sum(t2.item_num) as item_num 
from db_order.sn_order_20210412  t1
join db_order.sn_orderitem_20210412 t2 on t1.id =t2.order_id 
group by t1.member_id ;

查询结果

	member_id	money	order_num1	order_num2	order_num3	item_num
	1	600.9000092	6	6	1	8
	5	2400.300049	2	2	1	7
	12	198	2	2	1	3
	13	50.80000019	2	2	2	2
计数	4		12

  注意，以上查询出现逻辑错误：

1. 关联的时候，订单表的会员数比原来订单表的会员数是不是少了？为什么？ ——4 vs 14
2. 关联以后，订单数量多了 订单应该是24张 ——24 vs 12
3. 累计购买金额也不对——参看补充知识：笛卡尔积

简单分析问题

查询关联前的会员数及订单数量

-- 关联前会员查询
select distinct member_id
from db_order.sn_order_20210412;
select count(distinct member_id) as member_num
from db_order.sn_order_20210412;

查询结果

member_id	2	3	4	5	11	31	15	8	9	21	13	1	12	9999
会员数	14

-- 关联前订单及订单数量
select distinct id 
from db_order.sn_order_20210412;
select count(distinct id) 
from db_order.sn_order_20210412;

查询结果

id

A002

A003

A004

A005

A006

A007

A008

A009

A010

A011

A012

A014

A015

A016

A017

A018

A019

A020

A001

A2222

A00000

A11111

A22222

A5555

订单数量

24

 总结：内关联会导致匹配不上的数据丢失

外连接

场景：保留匹配不上的数据不丢失

左关联：左表的所有信息都包括进去

select 表1.字段A，表2.字段B

from 表名1

left(inner) join 表名2 on 关联字段

where……

右关联：右表的所有信息都包括进去

select *

from 表名1

right(inner) join 表名2 on 关联字段

where……

尝试用左关联解决实战案例：统计会员购买的金额，购买的订单数，购买的商品数

• 方案一 ：出现问题——累计金额数据丢失

-- 用 left join 把所有的会员信息保留
select member_id
      ,sum(t1.price*t1.item_num) as order_money  /*order_money数据是从订单明细表里取的，所以有的会员消费金额会丢失*/
      ,count(distinct t.id) as order_number
      ,sum(t1.item_num) as item_number           /*注意数据的意义，item_num已经表示商品数量，所以需要用求和函数sum而非计数函数count*/
from sn_order_20210412 t
left join db_order.sn_orderitem_20210412 t1 on t.id = t1.order_id 
group by member_id
order by sum(t1.item_num) desc;

查询结果

member_id	order_money	order_number	item_number
1	200.3	6	8
5	1,200.15	1	7
12	99	1	3
13	50.80000019	3	2
2	[NULL]	1	[NULL]
3	[NULL]	1	[NULL]
4	[NULL]	1	[NULL]
8	[NULL]	1	[NULL]
9	[NULL]	1	[NULL]
11	[NULL]	1	[NULL]
15	[NULL]	2	[NULL]
21	[NULL]	1	[NULL]
31	[NULL]	1	[NULL]
9,999	[NULL]	5	[NULL]
代码逻辑错误：order_money 数据丢失

• 方案二：出现问题——累计订单金额数据重复

-- 1、先构造一个子查询
select member_id 
      ,t.id 
      ,order_money 
      ,t1.item_num 
from db_order.sn_order_20210412 t
left join db_order.sn_orderitem_20210412 t1 on t1.order_id =t.id ;
-- 2、嵌套子查询
select member_id
      ,sum(order_money) as order_money  
      ,count(distinct t2.id) as order_number     /*从临时表里提取数据，t2.id*/
      ,sum(item_num) as item_number              /*不带表名？*/
from (select member_id 
      ,t.id 
      ,order_money 
      ,t1.item_num 
from db_order.sn_order_20210412 t
left join db_order.sn_orderitem_20210412 t1 on t1.order_id =t.id ) t2
group by member_id
order by sum(item_num) desc;

查询结果

member_id	order_money	order_number	item_number
1	1,088.65	6	8
5	2,400.30	1	7
12	198	1	3
13	53.10000014	3	2
2	100	1	[NULL]
3	12.10000038	1	[NULL]
4	200.1499939	1	[NULL]
8	100.1500015	1	[NULL]
9	20.14999962	1	[NULL]
11	0.150000006	1	[NULL]
15	130.8999996	2	[NULL]
21	110.1500015	1	[NULL]
31	1,215.10	1	[NULL]
9,999	420	5	[NULL]
逻辑错误：order_money 数据重复

• 方案三：解决了数据重复和数据丢失问题

select member_id
      ,sum(order_money) as order_money  
      ,count(distinct t.id) as order_number    
      ,sum(item_num) as item_number              
from db_order.sn_order_20210412 t
left join (select order_id 
                 ,sum(item_num) as item_num
           from db_order.sn_orderitem_20210412
           group by order_id) t1
     on t1.order_id = t.id
group by member_id
order by sum(item_num) desc;

查询结果

member_id	order_money	order_number	item_number
1	688.0500107	6	8
5	1,200.15	1	7
12	99	1	3
13	53.10000014	3	2
2	100	1	[NULL]
3	12.10000038	1	[NULL]
4	200.1499939	1	[NULL]
8	100.1500015	1	[NULL]
9	20.14999962	1	[NULL]
11	0.150000006	1	[NULL]
15	130.8999996	2	[NULL]
21	110.1500015	1	[NULL]
31	1,215.10	1	[NULL]
9,999	420	5	[NULL]

 逻辑错误原因分析及解决问题

补充知识——左关联构建的笛卡尔积表

1、先构建两个演示表

左表

订单号	订单金额	会员号	创建时间	订单状态
A005	1200.15	5	2017/1/1 13:23	2
A019	99	12	2017/2/11 13:23	1

 

 

 

 

右表

订单商品ID	订单号	商品ID	商品名称	商品价格	商品数量
AB004	A005	1001	A	0.15	1
AB005	A005	1004	D	200	6

 

 

 

 

2、构建笛卡尔积表

笛卡尔积表

订单号	订单金额	会员号	创建时间	订单状态	订单商品ID	订单号	商品ID	商品名称	商品价格	商品数量
A005	1200.15	5	2017/1/1 13:23	2	AB004	A005	1001	A	0.15	1
A005	1200.15	5	2017/1/1 13:23	2	AB005	A005	1004	D	200	6
A019	99	12	2017/2/11 13:23	1	AB004	A005	1001	A	0.15	1
A019	99	12	2017/2/11 13:23	1	AB005	A005	1004	D	200	6

 

 

 

 

 

 

笛卡尔积表做左关联后

订单号	订单金额	会员号	创建时间	订单状态	订单商品ID	订单号	商品ID	商品名称	商品价格	商品数量
A005	1200.15	5	2017/1/1 13:23	2	AB004	A005	1001	A	0.15	1
A005	1200.15	5	2017/1/1 13:23	2	AB005	A005	1004	D	200	6
A019	99	12	2017/2/11 13:23	1	null	null	null	null	null	null
出现问题：订单金额没有办法计算

 

 

 

 

 

 

3、修改bug——保持左表不动，构建一个临时表作为右表

左表

订单号	订单金额	会员号	创建时间	订单状态
A005	1200.15	5	2017/1/1 13:23	2
A019	99	12	2017/2/11 13:23	1

 

 

 

 

右表

订单号	商品数量
A005	7

 

 

 

4、把临时表和左表进行左关联

左关联

订单号	订单金额	会员号	创建时间	订单状态	订单号	商品数量
A005	1200.15	5	2017/1/1 13:23	2	A005	7
A019	99	12	2017/2/11 13:23	1	null	null
解决了数据重复和丢失的问题

 

 

 

 

 

注意事项：

• 针对内关联出现的数据缺失问题可以用外关联来解决
• 一般使用左关联，左关联是会保留我们左边表的所有信息；把数据比较全的表放在左边，避免数据丢失
• 很多时候统计的时候，外关联经常会结合子查询来使用，但是构建临时表时要先分析问题，避免出现逻辑错误
• 上述实战案例出现问题

1. 关联后直接sum(order_money)，会导致数据有重复
2. 关联后sum(t1.price*t1.item_num)，会导致数据丢失