• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 429
  • Last Modified:

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)


a.site_id = b.siteid

can someone shed some light on this?

  • 3
2 Solutions
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.

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
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.
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?
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.
@novicesybase, could this be closed out please?

Guidance on grading: What grade should I award?
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.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now