difference in syntax

what is the difference between

 a.site_id *= b.siteid (I am seeing that some stored procs are using this way to join two columns , am not understanding how it makes difference)

and

a.site_id = b.siteid

can someone shed some light on this?

Thanks
novicesybaseAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
a.site_id = b.siteid
is an inner join

The Transact-SQL outer join operators *= and =* are supported in Sybase IQ, in addition to the SQL/92 join syntax using a table expression in the FROM clause.
http://infocenter.sybase.com/archive/index.jsp?topic=/com.sybase.infocenter.dc38151.1260/html/iqref/iqref246.htm

a.site_id *= b.siteid
is an outer join (this syntax is no longer considered best practice)

ANSI join syntax is strongly recommended instead, eg

LEFT OUTER JOIN b ON a.site_id = b.siteid
0
 
alpmoonConnect With a Mentor Commented:
Sybase ASE supports *= and =* syntax as well. However, as Paul has mentioned ANSI join syntax is suggested, a query with *= syntax may behave differently if more than two tables are involved in join.
0
 
novicesybaseAuthor Commented:
actually by using *= syntax, would the performance of the stored proc degrade?

we have a stored proc, that is having frequent timeouts and we are asking to tune it. so i found in the stored procedure they are using this syntax. so just wanted to check removing this syntax will the performance improve?
0
 
PortletPaulfreelancerCommented:
An outer join is less efficient than an inner join, but the required logic may dictate that an outer join be used. However I would not expect use of *= to be any less efficient than the ANSI equivalent.

If you are amending that proc you should retire that old syntax, but - by itself- changing syntax is not going to magically improve performance.
0
 
PortletPaulfreelancerCommented:
@novicesybase, could this be closed out please?

Guidance on grading: What grade should I award?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.