# How to inverse spatial query [duplicate]

I have two tables with polygons (CTR_daim and CTA_LOW_1111). I have this query to select all polygons from CTR_daim that is intersected by any of the polygons from table CTA_LOW_1111

``````WITH
transform_t2 AS (SELECT *, ST_transform("CTA_LOW_1111".the_geom, 4619) AS geom
FROM "CTA_LOW_1111"),
transform_t1 AS (SELECT * FROM "CTR_daim")
SELECT DISTINCT(transform_t1.*), ST_asText(transform_t1.the_geom) AS geomastext FROM
transform_t1, transform_t2
WHERE ST_intersects(transform_t1.the_geom, transform_t2.geom)
``````

How can I "inverse" this query to select the polygons that are not intersected by any polygons in CTA_LOW_1111?

Left join on st_intersects and select rows where the right table rows are NULL:

``````select a.*
from public.table_a as a
left join public.table_b as b
on st_intersects(a.geom, b.geom)
where b.geom is null
``````

This will do what you require and use spatial indexes as well:

``````WITH
transform_t2 AS (SELECT *, ST_transform("CTA_LOW_1111".the_geom, 4619) AS geom
FROM "CTA_LOW_1111"), ,
transform_t1 AS (SELECT * FROM "CTR_daim")
SELECT DISTINCT(transform_t1.*), ST_asText(transform_t1.geom) AS geomastext
FROM  transform_t1
LEFT JOIN
transform_t2  ON
ST_Intersects(transform_t1.the_geom,transform_t2.geom)
WHERE transform_t2.id IS NULL;
``````

Here is an example I used to test for points, green=outside / red=inside any of the the polygons: