Unmatched records missing from spatial left join

Question

I have a polygons table and a points table. I want to get for each polygon the number of points located in it and have zero for polygons with no points. However with the following query I miss the polygons with zero points. I expected the LEFT JOIN to keep them though. I guess the st_transform is not causing this issue.

select plg.id,plg.geom,count(distinct pnt.id) as num_pnt
from polygons plg
left join points pnt
on st_contains(plg.geom,st_transform(pnt.geom,3003))
where pnt.fieldx = "xx"
group by plg.id,plg.geom;`

What am I doing wrong?

Answer 1

Try moving the where clause to a join condition:

select plg.id,p lg.geom, count(pnt.id) as num_pnt
from polygons plg
left join points pnt
on st_contains(plg.geom,st_transform(pnt.geom,3003)) and pnt.fieldx = 'xx' --Like this
group by plg.id, plg.geom;

See SQL JOIN - WHERE clause vs. ON clause

If you join first, then filter with a where clause, all rows where there are no match in the right table and rows where pnt.fieldx <> 'xx' will be removed from the join result.

Answer 2

The issue come from :

where pnt.fieldx = 'xx'

If no point are matching, pnt.fieldx is NULL, so the WHERE is not true. Just replace the where clause:

where pnt.fieldx = 'xx' or pnt.fieldx is NULL

Or add the condition in the join clause :

select plg.id,plg.geom,count(distinct pnt.id) as num_pnt
from polygons plg
left join points pnt on
     st_contains(plg.geom,st_transform(pnt.geom,3003)) 
     and pnt.fieldx = 'xx'
group by plg.id,plg.geom;