PostgreSQL PostGIS: Spatial join but keep all features that don't intersect

Question

Spatial joining a point table to a polygon table, creating a new output table. So far I can only get the output table to contain features that satisfy the spatial join. However, I want the output to contain ALL features, even the ones that don't intersect. I also want to choose which columns are pulled through into the final output (in the case below, 5 columns from 'topo' and only 1 from 'adb')

Here's my code as it stands at the moment:

DROP TABLE IF EXISTS public.wolv_topobuild_uprns_all;
CREATE TABLE public.wolv_topobuild_uprns_all AS
SELECT
  topo.id,
  topo.geom,
  topo.ogc_fid,
  topo.theme,
  topo.class,
  adb.uprn
FROM
  public.wolv_adb_slim as adb,
  public.wolv_topobuild as topo
WHERE
  ST_WITHIN(adb.geom, topo.geom)

From my research it seems I want to mix this spatial join with a full outer join, but being able to specify the columns I want in the output table.

I want the resulting output table to contain all of the original polygons but with the chosen metadata from the points where they fall inside a polygon. There will be some points that lie outside of the polygons... we can ignore them from the final output.

Answer 1

To obtain all topo rows, whether they have an adb or not, use LEFT OUTER JOIN:

SELECT
  topo.id,
  topo.geom,
  topo.ogc_fid,
  topo.theme,
  topo.class,
  adb.uprn
FROM
  public.wolv_topobuild as topo
LEFT OUTER JOIN
  public.wolv_adb_slim as adb ON ST_WITHIN(adb.geom, topo.geom)

Conversely, obtain all adb rows, whether they have a topo or not, use:

SELECT
  topo.id,
  topo.geom,
  topo.ogc_fid,
  topo.theme,
  topo.class,
  adb.uprn
FROM
  public.wolv_adb_slim as adb
LEFT OUTER JOIN
  public.wolv_topobuild as topo ON ST_WITHIN(adb.geom, topo.geom)

Note that points ON a polygon boundary will not have a match, whereas a ST_Intersects could return two boundaries in that case.

Best practice is to always use JOIN to join.