Thursday, May 29, 2014

SQL Left Join: Condition on where clause or on clause

As a database developer, we know about SQL left, right or inner join clearly. But when it comes to left join or right join with condition putting on where clause or on clause can make big  difference that we sometimes overlook or get panic why those making difference.
I will go directly to the example, e.g. we have Account and Customer table and we want all the accounts those are exist in Account table and also consider if any customer is added on '2013-05-27', we take him as well.


Here we have Account and Customer table and data look like below:

Account
Account_No
Status
Eff_DT
Customr_ID
123
actv_
21-05-2014
301
124
actv_
22-05-2014
307
125
actv_
23-05-2014
303


Customer
Customer_ID
Customer_Name
Eff_DT
301
john
21-05-2014
302
san
21-05-2014
303
paul
22-05-2014
304
helle
23-05-2014
305
jon
24-05-2014

SQL Query :
select Account_no, status,A. eff-dt, A.customer_id, Customer_Name
 from ACCOUNT as A
join customer as C on A.customer_id=C.customer_id
Result Set:
Account_No
Status
Eff_DT
Customr_ID
Customer_Name
123
actv_
21-05-2014
301
john
124
actv_
22-05-2014
307
null
125
actv_
23-05-2014
303
paul
Changes the query with 'on' clause:
select Account_no, status,A. eff-dt, A.customer_id, Customer_Name, C.EFF_DT as Customer_EFF_DT
 from ACCOUNT as A
join customer as C on A.customer_id=C.customer_id
and C.eff_dt= date '2013-05-24'
Result will look like:
Account_No
Status                        
Eff_DT
Customr_ID
Customer_Name
Customer_EFF_DT
123
actv_
21-05-2014
301
john
null
124
actv_
22-05-2014
307
null
null
125
actv_
23-05-2014
303
paul
null
Changes the query with 'Where' clause
select Account_no, status,A. eff-dt, A.customer_id, Customer_Name, C.EFF_DT as Customer_EFF_DT 
from ACCOUNT as A
join customer as C on A.customer_id=C.customer_id
where C.eff_dt= date '2013-05-24'
Result will return nothing
It means where clause filter after join has established. When we did same condition 
 C.eff_dt= date '2013-05-27' on clause it was still under left join on condition
and that returns you data as how left join work. But where clause work after making join, 
if we look at the last result, consider we are implying where clause on the last record set.