How to generate a column with a count of number of points in a polygon AND have 0 for the rows where there are no points in the polygon?

Question

I have two data sets with a large number of points that I’m organizing into a hexbin grid.

I'm associating these points with the hexbin polygons by using PostGIS to determine if a point falls within a hexbin polygon. The problem with my initial query is that if st_contains or st_intersects returned false, then I didn’t get the polygon geometry at all. It was simply a hexagon shaped hole in my map. What I wanted to return, instead, was the geometry and then a count of 0. So, when there is an intersection, return the polygon + the count (this is a common operation and many previous questions cover how to do this). But I also want to return the rows where st_disjoints is true and have the count (of points in the polygon) be 0. I can do this with these CTEs and a union:

with disjoint_table as (
    select
        a.the_geom_webmercator,
        a.cartodb_id,
        0 as count
    from
        hexbin_polygons_table a
        LEFT join points_table b on st_intersects(a.the_geom_webmercator, b.the_geom_webmercator)
    where
        b.the_geom_webmercator IS NULL
),
intersect_table as (
    select
        a.the_geom_webmercator,
        a.cartodb_id,
        count(b.the_geom_webmercator)
    from
        hexbin_polygons_table a
        join points_table b on st_contains(a.the_geom_webmercator, b.the_geom_webmercator)
    group by
        a.the_geom_webmercator,
        a.cartodb_id
)
select
    *
from
    disjoint_table
union
select
    *
from
    intersect_table

The disjoint_table part (left join where is NULL...etc.) comes from Paul Ramsey's answer here

This CTE + union works but is very slow. Is there a simpler or better way to get this sort of thing?

Answer 1

As @Vince mentions in comments, the key here is to include all non-matching rows from the joined relation in the result; this is trivially solved using a LEFT|RIGHT|FULL [OUTER] JOIN, where the thus denoted relation(s) will pass all (unfiltered) rows to the result set, with or without fulfilled match condition.
In the result set, 1:n matches will be represented as expected, having duplicated row selections from the joined relation for multiple matches of a row condition in the join relation, while non-matches of the joined table will have NULL values in join relation columns.

However, there's no need for a COALESCE here if you COUNT rows from the join relation explicitly, as an all-NULL set is counted as 0.

Run

SELECT ply.id,
       COUNT(pnt.*) AS cnt
FROM   <polygon> AS ply
LEFT JOIN
       <point> AS pnt
  ON   ST_Intersects(ply.geom, pnt.geom)
GROUP BY
       ply.id
;