In this gist, there was a mystery to be solved.... why did rows get eliminated when lateral correlation was used?

Using lateral correlation to combine SQL services.sql

--
-- Subject: Using lateral correlation to combine SQL services
-- Author: Scott Forstie  
-- Date  : January 9, 2022
-- Features Used : This Gist uses active_job_info, joblog_info, lateral correlation, and the values statement
--
--
stop;

--
-- What if we needed to:
-- a) Find active jobs using some filtering critera
--    (for example subsystem name)
-- b) Once we found the active jobs of interest, we needed to drill into the joblogs of these jobs and find something of interesst
--    (for example the most recent request type message)
-- c) Follow the best practice of going to the database one time with our query (when possible)
--
stop;

--
-- ========================================================
-- Step 1: Find the active jobs
-- ========================================================
--
select job_name, JOB_NAME_SHORT, AUTHORIZATION_NAME as USER_NAME, 
       FUNCTION_TYPE concat '-' concat "FUNCTION" as Function, JOB_STATUS
  from table (
      qsys2.active_job_info(SUBSYSTEM_LIST_FILTER => 'QINTER')
    );
stop;

--
-- =================================================================
-- Step 2: Laterally correlate to qsys2.joblog_info
--
-- Lateral allows values returned earlier in the query to be 
-- referenced later in the query.
-- =================================================================
--
select job_name, JOB_NAME_SHORT, AUTHORIZATION_NAME as USER_NAME, 
       message_text as last_request,
       FUNCTION_TYPE concat '-' concat "FUNCTION" as Function, JOB_STATUS
  from table (
         qsys2.active_job_info(SUBSYSTEM_LIST_FILTER => 'QINTER')
       ) AJ, lateral (
         select message_text
           from table (
               qsys2.joblog_info(AJ.JOB_NAME)
             ) jl
           where jl.MESSAGE_TYPE is not distinct from 'REQUEST'
           order by ordinal_position desc
           limit 1
       );
stop;

--
-- ========================================================
-- Look at the results closely!  There are fewer jobs.  
--
-- We lost the rows that have zero request messages!
-- ========================================================
--
stop;

--
-- =============================================================
-- Step 3: Adjust the lateral correlation to avoid losing rows
--
-- Regain the lost rows by unioning in a placeholder row using the VALUES statement
-- =============================================================
--
select job_name, JOB_NAME_SHORT, AUTHORIZATION_NAME as USER_NAME, 
       message_text as last_request,
       FUNCTION_TYPE concat '-' concat "FUNCTION" as Function, JOB_STATUS
  from table (
         qsys2.active_job_info(SUBSYSTEM_LIST_FILTER => 'QINTER')
       ) AJ, lateral (
         select ordinal_position, message_text
           from table (
               qsys2.joblog_info(AJ.JOB_NAME)
             ) jl
           where jl.MESSAGE_TYPE is not distinct from 'REQUEST'
         union all
         values (0, cast(null as varchar(80)))
           order by ordinal_position desc
           limit 1
       );
stop;

--
-- =============================================================================================================
-- Alternative: Use LOJ in combination with LATERAL
--
-- A left outer join (LOJ) includes the rows from the left table that were missing from the inner join.
-- =============================================================================================================
--
select job_name, JOB_NAME_SHORT, AUTHORIZATION_NAME as USER_NAME, 
       message_text as last_request,
       FUNCTION_TYPE concat '-' concat "FUNCTION" as Function, JOB_STATUS
  from table (
         qsys2.active_job_info(SUBSYSTEM_LIST_FILTER => 'QINTER')
       ) AJ  left outer join lateral (
         select message_text, aj.job_name as msg_job
           from table (
               qsys2.joblog_info(AJ.JOB_NAME)
             ) jl
           where jl.MESSAGE_TYPE is not distinct from 'REQUEST'
           order by ordinal_position desc
           limit 1
       ) on aj.job_name = msg_job;
stop;

You're very welcome Glenn. Its great to get this sort of feedback from the community.