sql: left join table a and b, outcome less than table a rows

Question

This is the code, help help

SELECT
    a.data_date AS `订单日期`,
    a.mid,
    a.item_id,
    a.sku_id,
    sum(a.ord_payd_cnt),
    sum(b.rfd_ord_cnt),
    sum(a.goods_payd_cnt),
    sum(b.rfd_goods_cnt),
    sum(a.ord_rel_payd_amt),
    sum(b.rfd_rel_amt)
FROM
    dwb_mall_ord_sku_tf_inc_d AS a
    LEFT JOIN dwb_mall_refund_sku_tf_inc_d AS b ON a.mid = b.mid
    AND a.item_id = b.item_id
    AND a.sku_id = b.sku_id
    AND a.data_date = b.data_date
WHERE
    a.log_date <= 20200820
    AND a.open_source = 'mall'
    AND a.ord_rel_payd_amt > 0
    AND a.shop_id = 2233
    AND b.log_date <= 20200820
    AND b.open_source = 'mall'
    AND b.rfd_rel_amt > 0
    AND b.shop_id = 2233
    AND a.item_id = 10000555
GROUP BY
    a.data_date,
    a.mid,
    a.item_id,
    a.sku_id

table a have 441 rows ,table have 44 rows ,but outcome 41 rows what's wrong with my code,can somebody help me!

score 1 · Answer 1 · answered Aug 21 '20 at 15:43

You are turning the outer join into an inner join because you have conditions on the second table in the where clause. More those conditions to the on clause:

FROM dwb_mall_ord_sku_tf_inc_d a LEFT JOIN dwb_mall_refund_sku_tf_inc_d b
     ON a.mid = b.mid AND
        a.item_id = b.item_id AND
        a.sku_id = b.sku_id AND
        a.data_date = b.data_date AND
        b.log_date <= 20200820 AND
        b.open_source = 'mall' AND
        b.rfd_rel_amt > 0 AND
        b.shop_id = 2233
WHERE a.log_date <= 20200820 AND
      a.open_source = 'mall' AND
      a.ord_rel_payd_amt > 0 AND
      a.shop_id = 2233 AND
      a.item_id = 10000555

Actually some of these conditions look like JOIN conditions, so:

FROM dwb_mall_ord_sku_tf_inc_d a LEFT JOIN dwb_mall_refund_sku_tf_inc_d b
     ON a.mid = b.mid AND
        a.item_id = b.item_id AND
        a.sku_id = b.sku_id AND
        a.data_date = b.data_date AND
        a.open_source = b.open_source AND
        a.shop_id = b.shop_id AND
        b.log_date <= 20200820 AND
        b.rfd_rel_amt > 0
WHERE a.log_date <= 20200820 AND
      a.open_source = 'mall' AND
      a.ord_rel_payd_amt > 0 AND
      a.shop_id = 2233 AND
      a.item_id = 10000555

You should also use meaningful table aliases, not arbitrary letters. I would suggest os and rs or something like that.

"You should also use meaningful table aliases, not arbitrary letters" This is a good advice ,thanks again — user8562499, Aug 21 '20 at 15:50

sql: left join table a and b, outcome less than table a rows

1 Answers1