Oracle SQL如何实现查询非空返回全部行、为空返回-1

核心逻辑是通过UNION ALL拼接原查询结果和兜底返回行，兜底行仅在原查询无匹配结果时生效，通用兼容绝大多数SQL数据库的写法如下：

-- 查询符合条件的所有ename
SELECT ename FROM emp WHERE job = 'MANAGER'
UNION ALL
-- 无匹配时的兜底行
SELECT '-1' AS ename
WHERE NOT EXISTS (SELECT 1 FROM emp WHERE job = 'MANAGER')

逻辑说明 #

• 当存在job = 'MANAGER'的匹配记录时，NOT EXISTS判断为假，UNION ALL后半段无返回，最终结果就是所有符合条件的ename，和原查询结果完全一致
• 当不存在job = 'MANAGER'的匹配记录时，UNION ALL前半段返回0行，后半段NOT EXISTS判断为真，返回1行-1，满足无结果时的返回要求

数据库适配说明 #

如果是Oracle数据库，需要给兜底查询指定dual系统表，写法调整为：

SELECT ename FROM emp WHERE job = 'MANAGER'
UNION ALL
SELECT '-1' AS ename FROM dual
WHERE NOT EXISTS (SELECT 1 FROM emp WHERE job = 'MANAGER')

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