Solved

difference in syntax

Posted on 2013-05-22
5
358 Views
Last Modified: 2013-06-17
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
0
Comment
Question by:novicesybase
  • 3
5 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 400 total points
ID: 39189564
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
 
LVL 13

Assisted Solution

by:alpmoon
alpmoon earned 100 total points
ID: 39189713
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
 

Author Comment

by:novicesybase
ID: 39192585
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39192806
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39252978
@novicesybase, could this be closed out please?

Guidance on grading: What grade should I award?
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

A procedure for exporting installed hotfix details of remote computers using powershell
Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now