PostgreSQL中UNION ALL合并后结果顺序异常的原因及解决方法

问题现象 #

在处理SQL题目时，两个PostgreSQL查询单独执行时排序结果符合预期，但使用UNION ALL合并后，第一个按Name排序的查询结果顺序异常，第二个按职业计数和首字母排序的查询顺序却看似正常：

第一个查询（单独执行正常） #

SELECT * 
FROM (
    SELECT CONCAT(Name, '(', LEFT(Occupation, 1), ')') 
    FROM OCCUPATIONS 
    ORDER BY Name
) AS sub1;

第二个查询（单独执行正常） #

SELECT * 
FROM (
    SELECT CONCAT('There are a total of ', COUNT(Occupation), ' ', LOWER(Occupation), 's.')
    FROM OCCUPATIONS
    GROUP BY Occupation
    ORDER BY COUNT(Occupation), LEFT(Occupation, 1)
) AS sub2;

合并后的查询（第一个子查询排序失效） #

SELECT dest 
FROM (
    SELECT * FROM (SELECT 1 as source, concat(Name,'(',LEFT(Occupation,1),')')  as dest
    FROM OCCUPATIONS 
    order by Name) as a
UNION ALL
    SELECT * FROM (SELECT 2 as source, concat('There are a total of ',count(Occupation),' ',lower(Occupation),'s.') as dest
    FROM OCCUPATIONS
    group by Occupation
    order by count(Occupation), left(Occupation,1)) as b
) as comb
order by source;

原因分析 #

1. SQL标准核心规则：数据库仅保证**最外层查询的ORDER BY**能决定最终结果的顺序。内层子查询中的ORDER BY在被包含到UNION ALL这类集合操作中时会被忽略——因为集合操作本身被定义为无序集合，内层排序无法传递到外层。
2. 第二个子查询的巧合：第二个子查询的GROUP BY在PostgreSQL的实现中，可能刚好以符合排序要求的顺序返回结果，但这是依赖具体执行计划的非标准行为，不具备可靠性，不能作为通用解决方案。

解决方案 #

要保证合并后的结果顺序符合预期，必须在最外层查询中统一指定完整的排序规则，同时从子查询中携带排序所需的字段：

修改后的合并查询 #

SELECT dest
FROM (
    -- 第一个子查询：携带Name字段用于后续排序
    SELECT 1 AS source,
           CONCAT(Name, '(', LEFT(Occupation, 1), ')') AS dest,
           Name AS sort_name,
           NULL AS sort_count,
           NULL AS sort_initial
    FROM OCCUPATIONS
    UNION ALL
    -- 第二个子查询：携带计数和首字母用于后续排序
    SELECT 2 AS source,
           CONCAT('There are a total of ', COUNT(Occupation), ' ', LOWER(Occupation), 's.') AS dest,
           NULL AS sort_name,
           COUNT(Occupation) AS sort_count,
           LEFT(Occupation, 1) AS sort_initial
    FROM OCCUPATIONS
    GROUP BY Occupation
) AS comb
-- 外层统一排序：先按source分组，再按各自规则排序
ORDER BY 
    source,
    -- 对第一部分按Name排序
    sort_name,
    -- 对第二部分按计数、首字母排序
    sort_count, sort_initial;

简化版本（利用字段排序兼容性） #

如果第一个子查询的dest字段排序逻辑和Name完全一致（即CONCAT(Name, '(', LEFT(Occupation,1), ')')的字典序等于Name的字典序），也可以直接用dest作为第一部分的排序键：

SELECT dest
FROM (
    SELECT 1 AS source, CONCAT(Name, '(', LEFT(Occupation, 1), ')') AS dest
    FROM OCCUPATIONS
    UNION ALL
    SELECT 2 AS source, CONCAT('There are a total of ', COUNT(Occupation), ' ', LOWER(Occupation), 's.') AS dest,
           COUNT(Occupation) AS sort_count, LEFT(Occupation, 1) AS sort_initial
    FROM OCCUPATIONS
    GROUP BY Occupation
) AS comb
ORDER BY 
    source,
    -- 第一部分按dest（等价于Name）排序
    CASE WHEN source = 1 THEN dest END,
    -- 第二部分按计数、首字母排序
    sort_count, sort_initial;

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