Solved

difference in syntax

Posted on 2013-05-22
5
403 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 49

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 49

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 49

Expert Comment

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

Guidance on grading: What grade should I award?
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
Resolving an irritating Remote Desktop connection that stops your saved credentials from being used.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

729 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