qgis - Join polygons by location PostgreSQL/PostGIS

I have two shapefiles - test7 and MD_Overlay

test7 has about 600,000 records with over one hundred attributes (about ~2GB) , so performing spatial joins in QGIS is very inefficient. I would like to find out the equivalent SQL command of QGIS Join attributes by location in PostGIS. My aim is to add the columns of MD_Overlay to test7 and to keep both matching and non-matching records.

This would be a polygons in polygons operation. (So a polygon in test7 would have to be fully within a polygon in MD_Overlay in order to be joined)

I have done much research but cannot find a solution. Any advice?

Answer

There are a few ways to do this, here's one:

SELECT t.*, m.*
FROM test7 AS t
LEFT JOIN MD_Overlay AS m
ON ST_Within(t.geom, m.geom)

LEFT JOIN: Return all rows from the left table, and the matched rows from the right table. The m.* columns will be null when there is no spatial intersection. You should ensure that both your geometry columns have spatial indexes.