locked
difference between these 2 queries RRS feed

  • Question

  • I have the following query that I would like to understand what is happening

    create table #tmpclients (id int not null)
    
    insert into #tmpclients values ('1')
    insert into #tmpclients values ('2')
    insert into #tmpclients values ('3')
    insert into #tmpclients values ('4')
    
    
    
    
    create table #tmpdata (id int not null,
    						trandate datetime not null,
    						tranamount money not null)
    						
    insert #tmpdata values ('1','2013-07-01',24.54)
    insert #tmpdata values ('1','2013-07-01',36.48)
    insert #tmpdata values ('2','2013-07-01',45.00)
    insert #tmpdata values ('2','2013-07-02',12.00)
    insert #tmpdata values ('2','2013-07-02',125.00)
    insert #tmpdata values ('3','2013-07-02',15.77)
    insert #tmpdata values ('3','2013-07-02',0.77)
    insert #tmpdata values ('3','2013-07-03',4.57)
    insert #tmpdata values ('4','2013-07-03',16.99)
    insert #tmpdata values ('4','2013-07-04',75.49)
    
    select a.id,isnull(sum(b.tranamount),0) as amount
    from #tmpclients as a
    left outer join #tmpdata as b
    on a.id=b.id 
    
    where b.trandate ='2013-07-02'
    group by a.id
    
    
    drop table #tmpclients
    drop table #tmpdata

    what I would like to happen is that with the left join any id's that don't have a transaction that is on the where clause will show as 0.00 but I only get 2 records back so it feels like the left join is a inner join.

    when I change the query to the following

    select a.id,isnull(sum(b.tranamount),0) as amount
    from #tmpclients as a
    left outer join #tmpdata as b
    on a.id=b.id and b.trandate ='2013-07-02'
    
    --where b.trandate ='2013-07-02'
    group by a.id

    I get the correct result but the question is why?

    Regards

    Tuesday, August 6, 2013 8:35 PM

Answers

  • in your first query..

    SQL1

    sql joins all your tmpclients id's with the tmpdata id's as the join validates to true and then it tries to eliminate the ones that dont satisfy your where clause...

    SQL2

    in your second statement sql tries to join our client id's with data id's along with the date, which it can find only in a small sub set and hence it returned zero's to 1 and 4.

    Essentially, when using outer joins, you need to be careful while using the where clause as the where clause does not execute as part of the outer join but it execute on the result set of the outer join.


    Nothing is Permanent... even Knowledge.... My Blog

    Tuesday, August 6, 2013 8:48 PM

All replies

  • in your first query..

    SQL1

    sql joins all your tmpclients id's with the tmpdata id's as the join validates to true and then it tries to eliminate the ones that dont satisfy your where clause...

    SQL2

    in your second statement sql tries to join our client id's with data id's along with the date, which it can find only in a small sub set and hence it returned zero's to 1 and 4.

    Essentially, when using outer joins, you need to be careful while using the where clause as the where clause does not execute as part of the outer join but it execute on the result set of the outer join.


    Nothing is Permanent... even Knowledge.... My Blog

    Tuesday, August 6, 2013 8:48 PM
  • Logically the WHERE clause is after the FROM clause.

    So, in first query the LEFT JOIN gets the matching rows with right hand table and non-matching rows with NULL values. On top of it WHERE clause is applied.

    In case of 2nd query, LEFT JOIN gets the matching rows with right hand table(including trandate ='2013-07-02') and non-matching rows with NULL values .

    If you want to get clear picture remove group by and execute the query, 

    select a.id,b.*
    from #tmpclients as a
    left outer join #tmpdata as b
    on a.id=b.id 
    where b.trandate ='2013-07-02'
    
    select a.id,b.*
    from #tmpclients as a
    left outer join #tmpdata as b
    on a.id=b.id and b.trandate ='2013-07-02'
    


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, August 7, 2013 5:24 AM