Can you paginate through 2 tables in parallel, with 1 query

Question

I want to paginate through 2 different sources. I think I can do this using the thing where u join 2 queries into one, however, I don't want to merge them in parallel.

Is it possible to select the size of the first query, so I can limit the size of the second query; allowing me to properly paginate; first through the first query; and if partial / no result; then paginate through the second query.

I could obviously implement this at my application level, but I would prefer a single query.

After thinking about it, I could maybe do something like this?

with a as (... offset :offset limit :limit), 
    a_count as (select count(*) from a)
    select * a 
    union
    select ...
    offset (select count from a_count)
    limit greatest(:limit - (select count from a_count), 0)

Is this efficient? What can I improve?

score 4 · Accepted Answer · answered Jan 30 '22 at 17:22

Simply:

SELECT 'a' AS source, * FROM a
UNION ALL                         -- NOT just UNION!!
SELECT 'b' AS source, * FROM b    -- matching row type!
OFFSET :offset
LIMIT  :limit;

The added source is optional.

Unless using parentheses, LIMIT / OFFSET apply to the combined result set. While combined with UNION ALL, and no outer ORDER BY, you get rows from table a first, and rows from table b next.

If you need a particular sort order, add ORDER BY per table with parentheses. Like:

(SELECT * FROM a ORDER BY foo)
UNION ALL
(SELECT * FROM b ORDER BY bar)
OFFSET :offset
LIMIT  :limit;

Still no outer ORDER BY!

However, LIMIT / OFFEST for pagination scales terribly for big tables. Consider instead:

Can you paginate through 2 tables in parallel, with 1 query

1 Answers1