Another method that works even in ancient versions (eg 5.1) is to mimic the LATERAL
join with what I call "poor man's LATERAL JOIN / OUTER APPLY".
This is the LATERAL join we want to mimic:
SELECT i.*, si.child, si.parent_id
FROM items AS i
LEFT JOIN LATERAL
( SELECT s.*
FROM sub_items AS s
WHERE s.parent_id = i.id
ORDER BY s.id -- assuming we don't want arbitrary results
LIMIT 2
) AS si
ON TRUE ; -- this is redundant: ON i.id = si.parent_id ;
The poor man's version:
SELECT i.*, si.child, si.parent_id
FROM items AS i
LEFT JOIN sub_items AS si
ON si.parent_id = i.id
AND si.id <= -- 1. three places where
COALESCE(
( SELECT s.id -- 2. same column is used
FROM sub_items AS s
WHERE s.parent_id = i.id
ORDER BY s.id -- 3. as this ORDER BY
LIMIT 1 OFFSET 1 -- pick the 2nd.
-- if you want LIMIT 5,
-- you need LIMIT 1 OFFSET 4 here
),
4294967295) -- this would depend on the type
-- of the column used in ORDER BY
-- and on ASC/DESC
;
Test at dbfiddle.uk
Disadvantages:
- complicated to write (and read/understand), especially when the LIMIT wanted is 2 or larger.
Advantages:
- works everywhere - even in ancient versions.
- adequate performance, when the number of distinct
items
(the parent table is small).