Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2006
    Posts
    8

    Unanswered: Difference between join and where condition

    Can anyone tell me what the difference is between these two statements?

    Code:
    select 
    	emp.employeeid,
    	vst.vestigingid
    from 
    	tblEmployee emp (nolock)
    		left join 
    	tblvestiging vst (nolock)
    		on    emp.vestigingid = vst.vestigingid 
    		and   emp.employeeid = 113
    Code:
    select 
    	emp.employeeid,
    	vst.vestigingid
    from 
    	tblEmployee emp (nolock)
    		left join 
    	tblvestiging vst (nolock)
    		on    emp.vestigingid = vst.vestigingid 
    where
            emp.employeeid = 113
    The first one returns all employees and disregards the extra condition. The second works just fine and only returns records for employeeid 113.

    Likewise, for the code below, the first one returns all records in the join, the second just works.

    Code:
    select 
    	emp.employeeid,
    	vst.vestigingid
    from 
    	tblEmployee emp (nolock)
    		left join 
    	tblvestiging vst (nolock)
    		on    emp.vestigingid = vst.vestigingid 
                    and	vst.vestigingid = 4
    Code:
    select 
    	emp.employeeid,
    	vst.vestigingid
    from 
    	tblEmployee emp (nolock)
    		left join 
    	tblvestiging vst (nolock)
    		on    emp.vestigingid = vst.vestigingid 
    where
    	vst.vestigingid = 4

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by intGod
    The first one returns all employees and disregards the extra condition. The second works just fine and only returns records for employeeid 113.
    They are both the same.

    The gist is, the condition is applied before the join when in the JOIN clause, and after the join when in the WHERE clause. So queries 1 & 2 are the same. Queries 3 & 4 are different.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2006
    Posts
    8
    Quote Originally Posted by pootle flump
    They are both the same.

    The gist is, the condition is applied before the join when in the JOIN clause, and after the join when in the WHERE clause. So queries 1 & 2 are the same. Queries 3 & 4 are different.
    Ah, but they are not. I also thought they were the same, but they're not. In the first case, all employee records joined upon the second table are returned. In the second case, I only get the records where employeeid = 113.

    Try it.

  4. #4
    Join Date
    Feb 2006
    Posts
    8
    Here's some sample code for you to try out

    Code:
    declare @tbLeft table
    (
    	leftid int,
    	rightId int
    )
    
    declare @tbRight table
    (
    	rightId int
    )
    
    declare @counter int
    select @counter = 1
    
    while @counter < 21
    begin
    	insert into @tbRight values(@counter)
    	select @counter = @counter + 1
    end
    
    select @counter = 1
    
    while @counter < 501
    begin
    	insert into @tbLeft values(@counter, rand()*20)
    	select @counter = @counter + 1
    end
    
    select
    	lft.leftId, lft.rightId
    from
    	@tbLeft	lft
    		left join
    	@tbRight rgt
    		on	lft.rightId = rgt.rightId
    		and	lft.leftId = 1
    
    select
    	lft.leftId, lft.rightId
    from
    	@tbLeft	lft
    		left join
    	@tbRight rgt
    		on	lft.rightId = rgt.rightId
    where
    	lft.leftId = 1

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just rolled my own. I beg your pardon - it does. I've never noticed that before.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2006
    Posts
    8
    Quote Originally Posted by pootle flump
    Just rolled my own. I beg your pardon - it does. I've never noticed that before.
    Me neither, and I'm stumped. No need for excuses - I should've provided sample code right away.

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Hey Int,
    Try reading Robert's blog post and Terry's mentioned articles. Yes, they are both DB2 guys, but they are talking about SQL and the same rules apply here. Catterall Consulting: Outer Join: Get the Predicates Right
    Dave

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    So queries 1 & 2 are the same.
    no, they're not

    query 1 is a left outer join but query 2 is effectively an inner join because the WHERE clause will throw away all unmatched rows from the left outer join which had NULL in the join column

    you knew this at one time, poots...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not when the filter is on the left table (you know which I mean by the left table - we've covered that one before too).
    It makes sense now I think on it, but I've not seen that used before (probably because there can't be much call for it).

    EDIT - to clarify it is specifically the behaviour of query #1 I haven't seen before.
    Last edited by pootle flump; 07-21-09 at 10:13.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    apologies, pootsie, you're right, i completely missed that the AND was on a column of the left table

    how weird that is, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2006
    Posts
    8
    I see now. It makes sense. Thanks for clearing that up!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •