Re: Count child objects for each line of a table: LEFT JOIN, LATERAL JOIN or subqueries ?

So you

Le lun. 23 mai 2022 à 15:14, Frank Streitzig <fstreitzig(at)gmx(dot)net> a écrit :

> Am Mon, May 23, 2022 at 01:55:07PM +0200 schrieb kimaidou:
> > Hi list,
> >
> > I have a basic need, often encountered in spatial analysis: I have a list
> > of cities, parks, childcare centres, schools. I need to count the number
> of
> > items for each city (0 if no item exists for this city)
> >
> > I have tested 3 different SQL queries to achieve this goal:
> >
> > * one with several LEFT JOINS: http://sqlfiddle.com/#!17/fe902/3
> > * one with sub-queries: http://sqlfiddle.com/#!17/fe902/4
> > * one with several LATERAL JOINS: http://sqlfiddle.com/#!17/fe902/6
>
> Hello,
>
> Cost of queries see link "View Execution Plan" in fiddle
>
> query 1: 134.62
> query 2: 8522.32
> query 3: 134.62
>
> query 1 and 3 have wrong count in result (columns nb_school,
> nb_childcare, nb_park)
>
> My try has cost of 81.83
>
> select c.*
> , coalesce(s.cnt,0) as cnt_school
> , s.schools
> , coalesce(cc.cnt,0) as cnt_childcare
> , cc.childcares
> , coalesce(p.cnt,0) as cnt_park
> , p.parks
> from city c
> left outer join
> (select fk_id_city, count(*) as cnt
> ,string_agg(name, ', ') AS schools
> from school
> group by fk_id_city) s
> on s.fk_id_city = c.id
> left outer join
> (select fk_id_city, count(*) as cnt
> ,string_agg(name, ', ') AS childcares
> from childcare
> group by fk_id_city) cc
> on cc.fk_id_city = c.id
> left outer join
> (select fk_id_city, count(*) as cnt
> ,string_agg(name, ', ') AS parks
> from park
> group by fk_id_city) p
> on p.fk_id_city = c.id
> order by c.id
> ;
>
> IMHO, but without a where clause, the cost will increase with the amount
> of data.
>
> Regards,
> Frank
>
>