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

多表关联

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

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

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

内关联

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

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

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 ;

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

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;

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

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

外连接

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

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

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;

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

-- 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;

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

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;

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

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

1、先构建两个演示表

 

 

 

 

 

 

 

 

2、构建笛卡尔积表

 

 

 

 

 

 

 

 

 

 

 

 

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

 

 

 

 

 

 

 

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

 

 

 

 

 

注意事项：

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

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