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?

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?

• Research the use of an OUTER JOIN to match even when no rows match, and the Coalesce() function to map NULL to zero. Aug 11 '20 at 0:53
• @Vince That approach makes sense. But getting FULL JOIN is only supported with merge-joinable or hash-joinable join conditions Aug 11 '20 at 15:43
• left outer join seems to be the winner Aug 13 '20 at 23:43

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
;
• If it were count(geom), then coalesce(count(geom),0) would be necessary. Aug 11 '20 at 12:42
• This was it. Now I know that count(table.*) works differently than count(table.column_a) Aug 11 '20 at 15:52
• @Vince COUNT cannot return NULL? Aug 11 '20 at 16:58
• Doh, yes. But that was the issue, indirectly. Aug 11 '20 at 17:03