How can I get the complement in this SQL query?

I have a bigquery database that has some latitude and longitude. Over this database, I run a query using CTE and the ST_COVERS bigquery function. The total sub-set is 518 rows and the points that apply to the CTE chain are 423. How can I get the remaining rows? I have tried to join, union all, left join, inner join, and other stuff, but I can’t get the total 518 rows.

PD: I’m not posting the query because it’s quite long (probably can be optimized a lot), but I can post it if it is really needed.

EDIT: This is the query (or most important parts of it). With this query, I can get all lat,lon points that are within some polygons. What I’m looking for is also to get the lat,lon points that are outside of all polygons and assign them a tag “NA” in the sector column.

WITH staging AS(
  SELECT id,
         lat,
         lon
  FROM
       lat_lon_table),

 area_1 AS(
 SELECT id,
        lat,
        lon,
        "area_1" AS sector,
        ST_COVERS(ST_GEOGFROMTEXT("POLYGON((#some lat lon coordinates))"),
        ST_GEOPOINT(lat,lon)) as `covers`
 FROM
     staging),

 area_2 AS(
 SELECT id,
        lat,
        lon,
        "area_2" AS sector,
        ST_COVERS(ST_GEOGFROMTEXT("POLYGON((#other lat lon coordinates))"),
        ST_GEOPOINT(lat,lon)) as `covers`
 FROM
     staging),

 area_3 AS(
 SELECT id,
        lat,
        lon,
        "area_3" AS sector,
        ST_COVERS(ST_GEOGFROMTEXT("POLYGON((#other lat lon coordinates))"),
        ST_GEOPOINT(lat,lon)) as `covers`
 FROM
     staging),

 area_4 AS(
 SELECT id,
        lat,
        lon,
        "area_4" AS sector,
        ST_COVERS(ST_GEOGFROMTEXT("POLYGON((#other lat lon coordinates))"),
        ST_GEOPOINT(lat,lon)) as `covers`
 FROM
     staging),

 union_all AS(
 SELECT * FROM area_1
 UNION ALL(SELECT * FROM area_2)
 UNION ALL(SELECT * FROM area_3)
 UNION ALL(SELECT * FROM area_4))

 SELECT 
     id,
     lat,
     lon,
     sector
 FROM
     union_all
 WHERE
     covers = TRUE

Answer

Try below

#standardSQL
WITH staging AS(
  SELECT id, lat, lon FROM lat_lon_table
), area_1 AS(
  SELECT id, lat, lon, "area_1" AS sector,
    ST_COVERS(ST_GEOGFROMTEXT("POLYGON((#some lat lon coordinates))"), ST_GEOGPOINT(lat,lon)) as `covers`
  FROM staging
), area_2 AS (
  SELECT id, lat, lon, "area_2" AS sector,
    ST_COVERS(ST_GEOGFROMTEXT("POLYGON((#other lat lon coordinates))"), ST_GEOGPOINT(lat,lon)) as `covers`
  FROM staging
), area_3 AS (
  SELECT id, lat, lon, "area_3" AS sector,
    ST_COVERS(ST_GEOGFROMTEXT("POLYGON((#other lat lon coordinates))"), ST_GEOGPOINT(lat,lon)) as `covers`
  FROM staging
), area_4 AS (
  SELECT id, lat, lon, "area_4" AS sector,
    ST_COVERS(ST_GEOGFROMTEXT("POLYGON((#other lat lon coordinates))"), ST_GEOGPOINT(lat,lon)) as `covers`
  FROM staging
), union_all AS (
  SELECT * FROM area_1 UNION ALL
  SELECT * FROM area_2 UNION ALL
  SELECT * FROM area_3 UNION ALL
  SELECT * FROM area_4
)
SELECT id, lat, lon, sector FROM union_all 
WHERE covers = TRUE
UNION ALL
SELECT id, lat, lon, 'NA' FROM lat_lon_table 
WHERE NOT id IN (SELECT DISTINCT id FROM union_all WHERE covers = TRUE)