Postgresql join with limit

Question

I am creating SQL query that involves multiple tables with 1 to N relation to support pagination.

To get the first 10 parents, I tried to do

SELECT * from parent p
LEFT JOIN child c
ON c.parent_id = p.id
LIMIT 10

This does not work if any parent has more than one children

One alternative I can do is

SELECT * from parent LIMIT 10 into temp_p;
SELECT * from temp_p p
LEFT JOIN child c
ON c.parent_id = p.id

This is pretty clumsy. What I would like to do is

SELECT * from parent p LIMIT 10
LEFT JOIN child c
ON c.parent_id = p.id

but of course the syntax is wrong. I am wondering if Postgresql have some way to support what I want to do.

Answer 1

Use a common table expression:

WITH ten_parents AS (
  SELECT * from parent LIMIT 10)
SELECT *
  FROM ten_parents p
    LEFT JOIN child c
      ON c.parent_id = p.id