CODEWITHSUNDEEP
sqldatabasesybaseouter-join
Clark Herlihy
Clark Herlihy

Reputation:

Am I translating Ansi OUTER JOIN syntax correctly to older Sybase (*=) join syntax?

Assuming this is the correct Ansi SQL syntax for a left outer join:

SELECT *  
FROM   employee  LEFT OUTER JOIN department  
          ON employee.DepartmentID = department.DepartmentID

And this is the correct Ansi SQL syntax for a right outer join:

SELECT * 
FROM   employee RIGHT OUTER JOIN department 
          ON employee.DepartmentID = department.DepartmentID

Is this the older Sybase equivalent of a left outer join:

SELECT * FROM employee, department
WHERE employee.DepartmentID *= department.DepartmentID

And this the older Sybase equivalent of a right outer join:

SELECT * FROM employee, department
WHERE employee.DepartmentID =* department.DepartmentID

So we put the * on the left side of the equals sign for a left outer join and on the right side of the equals sign for a right outer join.

Is that correct?

Upvotes: 2

Views: 4103

Answers (4)

amyxu
amyxu

Reputation: 41

yes. but sometimes these two may be a little different when handling set of result by where

Upvotes: 2

HLGEM
HLGEM

Reputation: 96572

Why would you be translating from left join to the older syntax, shouldn't you be translating from the older syntax to the preferred newer standard? I don't know about Sybase but since SQl Server is based on Sybase, I suspect it might have the same problem which is that the older syntax does not always correctly get intepreted as an outer join. At times the database might interpret it as a cross join, so in general I don't recommend using it unless you are accessing a database in such an old version that the newer syntax is not available.

Upvotes: 1

Vincent Buck
Vincent Buck

Reputation: 17142

*= is equivalent to a left outer join and ... =* to a right outer join (as you'd have guessed)

You may be interested to note that there is no support for *=* in older releases of Sybase ASE. Semantics and compatibility of Transact-SQL outer joins explains why (PDF)

Upvotes: 3

Scott Ivey
Scott Ivey

Reputation: 41568

Yes, that is correct

Upvotes: 1

Related Questions