Query the same without *= ? - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Query the same without *= ?

Author

Topic

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2014-01-31 : 09:42:38

Below, if I change the '*=' to '=', is it equivalent?

	UPDATE	tcr
	SET	TrustInvoiceID = 
            (Select Top 1 tfr.InvoiceID 
	    From #TFR tfr 
	    Where tfr.TrustTransactionID =* tcr.ID
            Order by tfr.ID Asc)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-01-31 : 17:26:51

They are not equivalent. I believe =* is a RIGHT JOIN and *= is LEFT JOIN. If you changed it to =, then it would be an INNER JOIN (or just JOIN).

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-01-31 : 17:39:22

*= is not per se equivalent to =, but in that specific situation, I think the UPDATE will get exactly the same result using = as it does with *=.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2014-02-03 : 09:44:44

quote:
Originally posted by ScottPletcher

*= is not per se equivalent to =, but in that specific situation, I think the UPDATE will get exactly the same result using = as it does with *=.

Thanks, that is what I thought.

I am still converting *= to left outer join syntax...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-04 : 07:21:25

quote:
Originally posted by denis_the_thief

quote:
Originally posted by ScottPletcher

*= is not per se equivalent to =, but in that specific situation, I think the UPDATE will get exactly the same result using = as it does with *=.

Thanks, that is what I thought.

I am still converting *= to left outer join syntax...

*= is no more supported and is including among breaking changes for latest versions of SQL server so its high time you convert *=,=* etc to LEFT, RIGHT join syntaxes

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

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2014-02-04 : 13:50:25

quote:
Originally posted by visakh16

quote:
Originally posted by denis_the_thief

quote:
Originally posted by ScottPletcher

*= is not per se equivalent to =, but in that specific situation, I think the UPDATE will get exactly the same result using = as it does with *=.

Thanks, that is what I thought.

I am still converting *= to left outer join syntax...

*= is no more supported and is including among breaking changes for latest versions of SQL server so its high time you convert *=,=* etc to LEFT, RIGHT join syntaxes

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

Yes, I agree. But we have mounds and mounds (and mounds) of code...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-11 : 01:33:27

quote:
Originally posted by denis_the_thief

quote:
Originally posted by visakh16

quote:
Originally posted by denis_the_thief

quote:
Originally posted by ScottPletcher

*= is not per se equivalent to =, but in that specific situation, I think the UPDATE will get exactly the same result using = as it does with *=.

Thanks, that is what I thought.

I am still converting *= to left outer join syntax...

*= is no more supported and is including among breaking changes for latest versions of SQL server so its high time you convert *=,=* etc to LEFT, RIGHT join syntaxes

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

Yes, I agree. But we have mounds and mounds (and mounds) of code...

you have to do it at some point of time so its better you start it at earliest rather than waiting for things to break.

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

Kristen
Test

22859 Posts

Posted - 2014-02-11 : 03:22:09

quote:
Originally posted by visakh16

you have to do it at some point of time so its better you start it at earliest rather than waiting for things to break.

I strongly disagree. I think it needs a structured project, full QA testing (re-testing), and rollout strategy. It would be much better to do it as part of a planned upgrade / migration / etc. otherwise there will be zero appetite amongst users for the effort of testing and vigilance for "new bugs" after rollout - unless there is some gain for them in terms of features etc.

If it is a "mound" of old code I suspect that there isn't a valid, current, automated test suite from way-back-then - which only makes the job worse. One of these joins converted wrongly is going to introduce a (possibly hard-to-find) bug that may be seriously damaging to corporate health, and its a mind numbingly boring job to change them all, so my expectation would be that mistakes are very likely.

However, it would have been better not to have used them in the first place - with the benefit of hindsight!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources