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

[CRPence <CRPbottle@xxxxxxxxx>]

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.