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 love the first one, with LEFT JOINS, because it is concise, seems simple, and allows querying easily more than one aggregated field from the child items. But we need to use the DISTINCT clause inside the aggregation functions (count, string_agg), in order to count each child only once.
The one with the subqueries seems the more common way (I have seen it a lot) but I find it cumbersome, and I guess it won't scale well for bigger datasets.
The one with the LATERAL joins seems overcomplicated, but I probably missed some easier way to use the lateral join. I do not know well how the LATERAL differs from the subqueries...
I would like to have your opinion on this scenario. What is the best query for this use case, considering the fact that it should perform well on heavier datasets (1 million cities and thousands of children).
NB: I used SQL Fiddle to help everyone see the data and SQL queries. Not sure if everyone can modify it or not. Please try to keep the 3 example unchanged. It seems SQL Fiddle has not been update since at least 2018, so PostgreSQL version is 9.6.
Regards
Michaël
