如何不使用UNION运算符改写SQL查询获取数据库全部城市

可以改写，最通用的ANSI SQL标准实现方案是使用全外连接（FULL OUTER JOIN），完全不需要UNION运算符，改写后的语句和原查询效果完全等价。

改写后的SQL语句 #

SELECT 
    COALESCE(c.City, s.City) AS City,
    COALESCE(c.Country, s.Country) AS Country
FROM (SELECT DISTINCT City, Country FROM Customers) c
FULL OUTER JOIN (SELECT DISTINCT City, Country FROM Suppliers) s
    ON c.City = s.City AND c.Country = s.Country
ORDER BY City, Country;

实现思路说明 #

• 原查询的核心逻辑是获取Customers和Suppliers两个表中所有不重复的（城市,国家）组合后排序，UNION的作用就是合并两个结果集并自动去重。
• 改写思路是先分别对两个表做去重处理，避免单表内的重复值产生多余的连接结果，再通过全外连接匹配两个去重后的结果集，连接条件为城市和国家完全相等。
• 全外连接会保留左右两个表中所有匹配/不匹配的行，再通过COALESCE函数取两个表中非空的城市、国家值，就能得到两个表中所有出现过的（城市,国家）组合，天然实现去重效果，和原UNION查询结果完全一致。

适用范围 #

该方案是ANSI SQL标准语法，PostgreSQL、SQL Server、Oracle等主流关系型数据库都支持。仅有少数不支持全外连接的数据库（比如MySQL 8.0之前的版本）无法直接使用，这类场景下可以通过临时表+NOT EXISTS过滤的方式实现，不过实现复杂度会更高。

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