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

Question

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?

Answer 1

Yes, that is correct

Answer 2

*= 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)

Answer 3

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.