Re: SYSPSTAT where source_type is not null is a poor performer

On 26-Feb-2015 13:42 -0600, Dan wrote:

Can you or anyone else explain what the following does? I've never
seen the LATERAL (aka TABLE) keyword and, even after reading up a
bit in the SQL Reference, I'm still not sure what this is doing:

FROM QSYS.QADBXATR A
, LATERAL
( SELECT *
FROM TABLE
( QSYS2.PARTITION_STATISTICS(A.DBXLIB, A.DBXFIL)
) AS X
) AS B

The LATERAL keyword effectively serves as an explicit attempt to inform the SQL that there is an intention to make column references that would otherwise be unsupported in that scope; the scope of a reference to a column normally would be available within a subquery [i.e. nested\under], but the request is made to allow a colum reference within another table-reference in the same query is to be scoped /laterally/. FWiW, apparently before the LATERAL kwd became a standard, the DB2 had already implemented the effect, but used the keyword TABLE, thus all of the DB2 variants apparently allow TABLE to function the same as LATERAL.

And while I have never read an explanation as to why, the implicit CROSS JOIN normally implied for the comma-separate table-references is *not* the effect seen for a query formed as the one shown above. Instead, the joined result is derived from just the one row being joined to to the results of the laterally referenced table, then joining each successive row of the first table-reference. Thus note that my past posts using a LATERAL were likely all composed using the JOIN syntax and specify join predicate(s) such as "ON t1.col=t2.col" but apparently that is unnecessary even if IMO clearer.