Solved

difference in syntax

Posted on 2013-05-22
5
367 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help with SQL query 3 2,201
Cannot create ODBC connection over the network 3 1,206
Clean up a mailbox 5 153
Executing multiple sybase statements in perl dbi 2 88
In the hope of saving someone else's sanity... About a year ago we bought a Cisco 1921 router with two ADSL/VDSL EHWIC cards to load balance local network traffic over the two broadband lines we have, but we couldn't get the routing to work consi…
Cloud-based technologies and services will continue to grow in popularity in 2017 thanks to the simple, scalable and cost-effective solutions they deliver. Here are three areas where cloud adoption is poised to really take off.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

910 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

21 Experts available now in Live!

Get 1:1 Help Now