Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE boxes (
  id   serial PRIMARY KEY
, name text
);

CREATE TABLE animals (
  id     serial PRIMARY KEY
, name   text
, age    numeric
, box_id bigint CONSTRAINT animals_boxes_id REFERENCES boxes
);

CREATE  TABLE vegetables (
  id     serial PRIMARY KEY
, name   text
, weight numeric
, box_id bigint CONSTRAINT vegatables_box_id REFERENCES boxes
);

INSERT INTO boxes (name) VALUES ('First box');
INSERT INTO animals (box_id, name, age) VALUES (1, 'Cat', 2);
INSERT INTO animals (box_id, name, age) VALUES (1, 'Cat', 3);
INSERT INTO animals (box_id, name, age) VALUES (1, 'Dog', 5);
INSERT INTO vegetables (box_id, name, weight) VALUES (1, 'Tomato', 20);
INSERT INTO vegetables (box_id, name, weight) VALUES (1, 'Cucumber', 30);
INSERT INTO vegetables (box_id, name, weight) VALUES (1, 'Potato', 50);

CREATE TABLE



CREATE TABLE



CREATE TABLE



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


-- faster for a small selection
SELECT b.name AS box_name, a.*, v.*
FROM   boxes b
LEFT  JOIN LATERAL (
   SELECT string_agg(a.name, ', ' ORDER BY a.id) AS animal_names
   FROM   animals a
   WHERE  a.box_id = b.id
   ) a ON true
LEFT  JOIN LATERAL (
   SELECT string_agg(v.name, ', ' ORDER BY v.id) AS vegetable_names
   FROM   vegetables v
   WHERE  v.box_id = b.id
   ) v ON true;


box_name
animal_names
vegetable_names


First box
Cat, Cat, Dog
Tomato, Cucumber, Potato



SELECT 1


-- the same with correlated subqueries
SELECT b.name AS box_name
    , (SELECT string_agg(a.name, ', ' ORDER BY a.id)
       FROM   animals a
       WHERE  a.box_id = b.id)  AS animal_names
    , (SELECT string_agg(v.name, ', ' ORDER BY v.id)
       FROM   vegetables v
       WHERE  v.box_id = b.id) AS vegetable_names
FROM   boxes b;


box_name
animal_names
vegetable_names


First box
Cat, Cat, Dog
Tomato, Cucumber, Potato



SELECT 1


-- faster for whole table
SELECT b.name AS box_name, a.animal_names, v.vegetable_names
FROM   boxes b
LEFT   JOIN (
   SELECT box_id, string_agg(a.name, ', ') AS animal_names   
   FROM  (
      SELECT box_id, id, name
      FROM   animals a
      ORDER  BY box_id, id
      ) a
   GROUP  BY 1
   ) a ON a.box_id = b.id
LEFT   JOIN (
   SELECT box_id, string_agg(v.name, ', ') AS vegetable_names 
   FROM  (
      SELECT box_id, id, name
      FROM   vegetables v
      ORDER  BY box_id, id
      ) v
   GROUP  BY 1   
   ) v ON v.box_id = b.id;


box_name
animal_names
vegetable_names


First box
Cat, Cat, Dog
Tomato, Cucumber, Potato



SELECT 1


-- wrong
SELECT b.name                                 AS box_name,
       string_agg(a.name, ', ' ORDER BY a.id) AS animal_names,
       string_agg(v.name, ', ' ORDER BY v.id) AS vegetable_names
FROM boxes AS b
LEFT JOIN animals a ON b.id = a.box_id
LEFT JOIN vegetables v ON b.id = v.box_id
GROUP BY b.name;


box_name
animal_names
vegetable_names


First box
Cat, Cat, Cat, Cat, Cat, Cat, Dog, Dog, Dog
Tomato, Tomato, Tomato, Cucumber, Cucumber, Cucumber, Potato, Potato, Potato



SELECT 1