# 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, 2020 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, 2020 at 15:43
• `left outer join` seems to be the winner Aug 13, 2020 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, 2020 at 12:42
• This was it. Now I know that `count(table.*)` works differently than `count(table.column_a)` Aug 11, 2020 at 15:52
• @Vince `COUNT` cannot return `NULL`? Aug 11, 2020 at 16:58
• Doh, yes. But that was the issue, indirectly. Aug 11, 2020 at 17:03