PostgreSQL如何移除查询结果中含空值列的返回行

现有如下三层左关联查询行政区划数据的SQL语句：

SELECT * FROM 
        (select x.place_id as id_1, x.name as name_1 
        from n_hanhchinhxa p join hanhchinhxa_x x on p.osm_id = x.osm_id) as dad
        LEFT JOIN 
        (select x.parent_place_id as parent_1, x.place_id as id_2, x.name as name_2 
        from n_hanhchinhxa p join hanhchinhxa_x x on p.osm_id = x.osm_id)  as tmpPlaceId 
        on tmpPlaceId.parent_1 = dad.id_1
        LEFT JOIN 
        (select p.osm_id as osm_id_3, x.place_id as id_3, p.name as name_3, x.parent_place_id,x.geometry 
         from n_hanhchinhxa p join hanhchinhxa_x x on p.osm_id = x.osm_id) as tmp 
         on tmp.parent_place_id = tmpPlaceId.id_2;

查询返回结果中存在空值行，其中osm_id_3、id_3为非重复值字段，之前尝试使用DISTINCT关键字未达到移除空行的效果。

• 空行产生的根源是LEFT JOIN的关联逻辑：左连接会保留左表全部记录，右表匹配不到关联条件时，对应右表的所有字段会返回NULL值。
• DISTINCT不生效的原因：该关键字作用是对结果集做整行去重，仅会删除完全一模一样的重复行，不具备过滤NULL值的能力，自然无法移除空列行。

你可以任选以下两种方法实现需求：

方案1：替换最后一层关联为INNER JOIN（推荐，效率更高） #

将最后关联tmp表的LEFT JOIN改为INNER JOIN，数据库只会返回两边关联条件匹配成功的记录，从根源上不会生成tmp表字段为NULL的空行，修改后的SQL如下：

SELECT * FROM 
        (select x.place_id as id_1, x.name as name_1 
        from n_hanhchinhxa p join hanhchinhxa_x x on p.osm_id = x.osm_id) as dad
        LEFT JOIN 
        (select x.parent_place_id as parent_1, x.place_id as id_2, x.name as name_2 
        from n_hanhchinhxa p join hanhchinhxa_x x on p.osm_id = x.osm_id)  as tmpPlaceId 
        on tmpPlaceId.parent_1 = dad.id_1
        -- 替换为INNER JOIN过滤无匹配的空记录
        INNER JOIN 
        (select p.osm_id as osm_id_3, x.place_id as id_3, p.name as name_3, x.parent_place_id,x.geometry 
         from n_hanhchinhxa p join hanhchinhxa_x x on p.osm_id = x.osm_id) as tmp 
         on tmp.parent_place_id = tmpPlaceId.id_2;

方案2：新增WHERE条件过滤空值 #

如果需要保留原有左连接逻辑，直接在查询末尾新增WHERE条件，过滤掉指定的非重复空值字段即可，修改后的SQL如下：

SELECT * FROM 
        (select x.place_id as id_1, x.name as name_1 
        from n_hanhchinhxa p join hanhchinhxa_x x on p.osm_id = x.osm_id) as dad
        LEFT JOIN 
        (select x.parent_place_id as parent_1, x.place_id as id_2, x.name as name_2 
        from n_hanhchinhxa p join hanhchinhxa_x x on p.osm_id = x.osm_id)  as tmpPlaceId 
        on tmpPlaceId.parent_1 = dad.id_1
        LEFT JOIN 
        (select p.osm_id as osm_id_3, x.place_id as id_3, p.name as name_3, x.parent_place_id,x.geometry 
         from n_hanhchinhxa p join hanhchinhxa_x x on p.osm_id = x.osm_id) as tmp 
         on tmp.parent_place_id = tmpPlaceId.id_2
-- 新增过滤条件，两个非重复字段非空即代表关联到有效数据
WHERE tmp.osm_id_3 IS NOT NULL AND tmp.id_3 IS NOT NULL;

补充说明：如果你需要把中间tmpPlaceId关联不到产生的空行也一并移除，可以把第二层的LEFT JOIN也替换为INNER JOIN即可。

内容的提问来源于stack exchange，提问作者Nguyễn Hoài Nam