SQL空间查询问题：点不在多边形内无返回结果的解决

我编写了一段SQL语句，可基于经纬度生成点，并根据点的位置返回对应结果：若点位于具备特定属性值的多边形区域内，则返回相应结果（逻辑详见下方CASE语句）：

SELECT
CASE
        WHEN p.column1 IS NOT NULL AND p.column2 = 'Yes' THEN 'Response_A'
        WHEN p.column1 IS NOT NULL AND p.column2 = 'No' THEN 'Response_B'
        WHEN p.column1 IS NOT NULL AND p.column2 IS NULL THEN 'Response_C'
        ELSE 'Outside Polygon'
    END AS Status,
    p.column1,
    p.column2,
    p.geometry
FROM
    my.table AS p
WHERE ST_Intersects(ST_SetSRID(ST_MakePoint(LONGITUDE, LATITUDE), 4326),
    ST_Transform(p.geometry, 4326))

但实际运行时发现问题：当点不在任何多边形内时，查询返回空表，而非预期的'Outside Polygon'。已知在多边形内的经纬度能正常得到结果，但不在多边形内的经纬度完全没有返回数据，ELSE语句根本没生效。

我尝试了以下几种方法，都没能解决问题：

• 将ST_Intersects替换为ST_Within，问题依旧；
• 添加CASE分支：

WHEN NOT ST_Within(ST_SetSRID(ST_MakePoint(LONGITUDE, LATITUDE), 4326), p.geometry) THEN 'Outside Polygon'

• 尝试自左连接，但未成功。

最终我找到了可行的查询语句：

SELECT
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM my.table AS p
            WHERE
                ST_Intersects(ST_SetSRID(ST_MakePoint(longitude, latitude), 4326), ST_Transform(p.geometry, 4326))
                AND p.column1 IS NOT NULL
                AND p.column2 = 'Yes'
        ) THEN 'Response A'
        WHEN EXISTS (
            SELECT 1
            FROM my.table AS p
            WHERE
                ST_Intersects(ST_SetSRID(ST_MakePoint(longitude, latitude), 4326), ST_Transform(p.geometry, 4326))
                AND p.column1 IS NOT NULL
                AND p.column2 = 'No'
        ) THEN 'Response B'
        WHEN EXISTS (
            SELECT 1
            FROM my.table AS p
            WHERE
                ST_Intersects(ST_SetSRID(ST_MakePoint(longitude, latitude), 4326), ST_Transform(p.geometry, 4326))
                AND p.column1 IS NOT NULL
                AND p.column2 IS NULL
        ) THEN 'Response C'
        WHEN NOT EXISTS (
            SELECT 1
            FROM my.table AS p
            WHERE
                ST_Intersects(ST_SetSRID(ST_MakePoint(longitude, latitude), 4326), ST_Transform(p.geometry, 4326))
        ) THEN 'Outside Polygon'
        ELSE 'Error'
    END AS Status,
    (
        SELECT ST_SetSRID(ST_MakePoint(longitude, latitude), 4326) AS point
    ) AS CreatedPoint;

内容的提问来源于stack exchange，提问作者I_suck_at_sql