เพิ่งรู้ว่า
select a.key1, a.key2
,sum(col_1)::int
from TabA a left outer join TabB b
on a.cust_numb = b.cust_numb
and a.subr_numb = b.subr_numb
and b.data_dt = '2009-01-01'
and a.store_cd = 'STORE1'
group by 1,2
นอกจากจะช้านรกแล้ว ยังให้ผลลัพธ์ผิดอีกด้วย ผลลัพธ์ที่ได้จะมี store_cd อื่นๆ นอกเหนือจาก STORE1 ปนมาด้วย ถ้าอยากให้ถูกต้อง ต้องเขียนแบบนี้
select a.key1, a.key2
,sum(col_1)::int
from TabA a, outer TabB b
where a.key1 = b.key1
and a.key2 = b.key2
and b.data_dt = '2009-01-01'
and a.store_cd = 'STORE1'
group by 1,2
ว่างๆ จะหาเหตุผลว่า ทำไม query แรกมันถึงผิด?
1 comment:
Normally we use the filter criteria in ‘where’ condition. If you use in the ‘on’ condition like your first query, the result will be the same as ‘where’ when you use inner join. But the result will be total difference when you use outer join. For more clearly understand, please see the explanation below:
Table A:
cust_numb subr_numb store_cd
1 1 STORE1
1 1 STORE2
1 1 STORE3
Table B:
cust_numb subr_numb date
1 1 2009-01-01
1 1 2009-01-02
=========================================================
Query1:
select a.cust_numb, a.subr_numb, a.store_cd, b.date
from Table_A a left outer join Table_B b
on a.cust_numb = b.cust_numb
and a.subr_numb = b.subr_numb
Result1:
A.cust_numb A.subr_numb A.store_cd B.date
1 1 STORE1 2009-01-01
1 1 STORE1 2009-01-02
1 1 STORE2 2009-01-01
1 1 STORE2 2009-01-02
1 1 STORE3
=========================================================
Query2:
select a.cust_numb, a.subr_numb, a.store_cd, b.date
from Table_A a left outer join Table_B b
on a.cust_numb = b.cust_numb
and a.subr_numb = b.subr_numb
and A.store_cd = ‘STORE1′
and B.Date = ‘2009-01-01′
Result2:
A.cust_numb A.subr_numb A.store_cd B.date
1 1 STORE1 2009-01-01
1 1 STORE1
1 1 STORE2
1 1 STORE2
1 1 STORE3
=========================================================
Query3:
select a.cust_numb, a.subr_numb, a.store_cd, b.date
from Table_A a left outer join Table_B b
on a.cust_numb = b.cust_numb
and a.subr_numb = b.subr_numb
where A.store_cd = ‘STORE1′
and B.Date = ‘2009-01-01′
Result3:
A.cust_numb A.subr_numb A.store_cd B.date
1 1 STORE1 2009-01-01
=========================================================
**According to result2, you will see that the condition is for consider null value on table_B. The records are not removed in the same way like you set it in where clause as result3.
Post a Comment