Sybase 16 Upgrade - nested outer joins require Lateral keyword

[Former Member]

Hello.

An upcoming databse upgrade to Sybase 16 has brought attention to the need for the lateral keyword with nested outer join statements. (I think this may have been introduced with a version before 16.) I have found very little documentation to explain this requirement. I have tested a few sqls with left outer joins and they seem to be returning the expected results even without the Lateral keyword. Is this a reliable practice to continue the use of outer joins without the lateral keyword? Can a detailed explanation be provided as to how these types of sql statements should be handled with Sybase 16?

[VolkerBarth]

Why would you think that nested outer joins need lateral?

You surely can "stack" outer joins like this (untested)

FROM ((t1 left outer join t2 on t1.pk = t2.pk)
   left outer join t3 on t1.pk = t3.pk)
       left outer join t4 on t1.pk = t4.pk...

IMHO, LATERAL is only needed for particular cases like joining with a stored procedure when the parameter are parts of the join, too.

[Former Member]

I had read this in some of the documentation and it seemed to indicate it was no longer supported. I am looking for either clarification and/or explanation on the issue.

"References to table expressions preceding in the FROM clause may now be used in ON clauses of nested outer joins. In previous releases, outer references in the ON phrase were permitted. Such outer references must now be indicated by use of the LATERAL keyword. The restriction enforces clarity and conforms to the SQL/99 standard. The following query is an example of one that is no longer valid, as it contains an outer reference (highlighted) without use of the LATERAL keyword: SELECT * FROM T1, T2 LEFT OUTER JOIN ( T3 LEFT OUTER JOIN T4 ON T1.col1 = T2.col2 ) ON T1.col2 = T2.col2)"

[Breck_Carter]

I have fond (not!) memories of being shocked and confused when reading that for the first time. Sadly, I don't remember much from 2003, but that sticks... see my answer below.

[Breck_Carter · Accepted solution]

I believe you may have misread a behavior change made in Version 9: "... The following query is an example of one that is no longer valid, as it contains an outer reference (highlighted) without use of the LATERAL keyword: ..."

Don't confuse "outer reference" with "outer join".

An outer join is a SQL join clause like LEFT OUTER JOIN.

An outer reference is an English documentation term for a reference inside a ( nested table expression ) to an object in the outer statement. That's what the Help is talking about when it says "You must use a lateral derived table to use an outer reference in the FROM clause."

So, no, there is no requirement to use the LATERAL keyword with outer joins.

---

FROM clause

lateral-derived-table - A derived table, stored procedure, or joined table that may include references to objects in the parent statement (outer references). You must use a lateral derived table to use an outer reference in the FROM clause.

You can use outer references only to tables that precede the lateral derived table in the FROM clause. For example, you cannot use an outer reference to an item in the SELECT list.

The table and the outer reference must be separated by a comma. For example, the following queries are valid:

 SELECT *
 FROM A, LATERAL( B LEFT OUTER JOIN C ON ( A.x = B.x ) ) myLateralDT;

SELECT *
 FROM A, LATERAL( SELECT * FROM B WHERE A.x = B.x ) myLateralDT;

SELECT *
 FROM A, LATERAL( procedure-name( A.x ) ) myLateralDT;

Specifying LATERAL (table-expression) is equivalent to specifying LATERAL (SELECT * FROM table-expression).

[Former Member · Accepted solution]

This is the clarification that I was looking for. I appreciate the very understandable explanation. Thank you.

[VolkerBarth · Accepted solution]

@JWinings: Well, feel free then to accept Breck's good answer, as described here:

How do I accept an answer to my question?