Re: can lateral be made to function like left outer join?

On 19 Feb 2013 11:31, Steve Richter wrote:

On Tue, Feb 19, 2013 at 1:51 PM, CRPence wrote:

The following functions to get the results of the NTE using the
FETCH FIRST clause, as tested on v5r3:

SELECT a.srcdta, b.ibmreqd
from QRPGLESRC a
left outer join
(select * from sysibm/sysdummy1 where ibmreqd='X'
fetch first row only) b
on /* 1=1 or... */ srcseq>0

that is neat. I never tried to join to a subselect. And you are
right. you do not have to join using keys because there is only
1 row to join to.

Actually in that example there are zero rows to which the primary is joined. I should have used the predicate 1<>1 on the WHERE-clause vs ibmreqd='X' to make that more conspicuous. I had changed the constant from 'N' in the original query to use the constant 'X' in the above query, having known the idea behind IBMreqd was an answer to a question [i.e. 'Y' or 'N' in English], but in hindsight I understand how easily that could be missed.

The reason having specified two table-reference separated by a comma in the FROM-clause of the original example produced no rows, is because the implicit join is a CROSS JOIN; i.e. a Cartesian product. A product for which a join of zero-to-many-rows to zero-rows is always an empty set much like the product of anything multiplied by zero is always zero.

The result of a LEFT OUTER JOIN is always at least the full set of the primary irrespective the number of rows in the secondary [as long as selection does not limit the rows from the primary]. Thus the join condition of "ON 1=1" [or for what should be the effectively identical outcome under normal circumstances, using "ON srcseq>0"] gives all rows from the primary with the NULL value for any "matching" rows in the secondary just as it would for any non-matching rows; i.e. because there are zero matching rows, a NULL row is generated to show the non-matching condition, for each row of the primary. When there is one row in the secondary, the result of the LEFT OUTER JOIN ON 1=1 will look the same as the CROSS JOIN.