Skip to main content
added 10 characters in body
Source Link
ypercubeᵀᴹ
  • 98.6k
  • 13
  • 215
  • 305

Another method that works even in ancient versions (eg 5.1) is to mimic the LATERAL join with what I call "poor man'sman's" LATERAL JOIN / OUTER APPLY"APPLY.

  • works everywhere - even in ancient versions.
  • adequate performance, when the number of distinct items (the parent table) is small) to medium.

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".

  • works everywhere - even in ancient versions.
  • adequate performance, when the number of distinct items (the parent table is small).

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.

  • works everywhere - even in ancient versions.
  • adequate performance, when the number of distinct items (the parent table) is small to medium.
Source Link
ypercubeᵀᴹ
  • 98.6k
  • 13
  • 215
  • 305

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).
Post Made Community Wiki by ypercubeᵀᴹ