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?
asked 09 Jul '13, 10:22
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.
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).
answered 09 Jul '13, 12:35