changing from implicit join to explicit join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

changing from implicit join to explicit join

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2013-12-24 : 15:31:15

Hi All,

we are trying to migrate from sql 2005 to 2012. I am changing one of the implicit join to explicit join. As soon as I change the join, the number of rows returned are fewer than before.

Below is my Implict join query



INSERT #RIF_TEMP1 (rf1_row_no,rf1_rif, rf1_key_id_no, rf1_last_date, rf1_start_date)
SELECT currow.rf0_row_no, currow.rf0_rif, currow.rf0_key_id_no, prevrow.rf0_start_date, currow.rf0_start_date
FROM #RIF_TEMP0 currow , #RIF_TEMP0 prevrow      
 
WHERE    
		currow.rf0_row_no = prevrow.rf0_row_no + 1   
		and currow.rf0_key_id_no = prevrow.rf0_key_id_no

DECLARE @countTemp  int
select @countTemp = count(*) from #RIF_TEMP1
PRINT @countTemp 

and below is explict join query

INSERT #RIF_TEMP1 (rf1_row_no,rf1_rif, rf1_key_id_no, rf1_last_date, rf1_start_date)
SELECT currow.rf0_row_no, currow.rf0_rif, currow.rf0_key_id_no, prevrow.rf0_start_date, currow.rf0_start_date
FROM #RIF_TEMP0 currow LEFT JOIN #RIF_TEMP0 prevrow      
 ON (currow.rf0_row_no = prevrow.rf0_row_no + 1)
WHERE    
		   
		 currow.rf0_key_id_no = prevrow.rf0_key_id_no

DECLARE @countTemp  int
select @countTemp = count(*) from #RIF_TEMP1
PRINT @countTemp

the count returned from both the queries is different.

I am not sure what am I doing wrong. The count of #RIF_TEMP0 is always 32, it never changes, but the variable @countTemp is different for both the queries.

Thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-24 : 15:56:58

Hard to tell without sample data, but your WHERE clause on the second query (LEFT OUTER JOIN) is making it an INNER JOIN. Try moving the predicate onto the join clause maybe?

 ON currow.rf0_row_no = prevrow.rf0_row_no + 1
 AND currow.rf0_key_id_no = prevrow.rf0_key_id_no

anjali5
Posting Yak Master

121 Posts

Posted - 2013-12-24 : 16:01:31

Thank you!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-25 : 00:55:50

see this

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

anjali5
Posting Yak Master

121 Posts

Posted - 2013-12-26 : 14:10:53

I read the article that you posted here. I did the same thing in my another query and still getting wrong results. Below is the query that has implicit join


insert #Status(name, sn, key, sname, sn, pn, mfr_date)
select  b.abbr,  b.sn,  b.key_id_no, a.name_abbr, a.sn, a.pn_mk_mod, a.key_id_no 
from STATUS a ,STATUS b  

WHERE
 a.key *= b.key_id_no  AND
 a.name = @in_sect  AND
 b.name = 'TEST'

I need to put the explicit join in below



insert #Status(name, sn, key, sname, sn, pn, mfr_date)
select  b.abbr,  b.sn,  b.key_id_no, a.name_abbr, a.sn, a.pn_mk_mod, a.key_id_no 
from STATUS a LEFT JOIN STATUS b  
  ON(a.key = b.key_id_no AND a.name = @in_sect  AND  b.name = 'TEST')

I am not sure what am I doing wrong

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-26 : 14:18:30

Nope the posted queries are not equivalent
the first query is doing an INNER join so corresponding explicit join query should be this


insert #Status(name, sn, key, sname, sn, pn, mfr_date)
select  b.abbr,  b.sn,  b.key_id_no, a.name_abbr, a.sn, a.pn_mk_mod, a.key_id_no 
from STATUS a INNER JOIN STATUS b  
  ON a.key = b.key_id_no AND a.name = @in_sect  AND  b.name = 'TEST'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

anjali5
Posting Yak Master

121 Posts

Posted - 2013-12-26 : 14:28:12

sorry, I forgot to put the *= for the original query. I just edited my post.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-28 : 01:58:25

can you post some data and explain the issue? without which i dont think we will be able to suggest anything further.
post sample data and output out of the two queries and also your required output.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources