SQL Developer中如何用单条查询实现无匹配时返回null值行

直接使用如下单条SQL即可满足需求，兼容MySQL、PostgreSQL、SQL Server等主流数据库：

SELECT * FROM subject 
WHERE subject_id = x OR subject_id IS NULL
ORDER BY subject_id IS NULL ASC 
LIMIT 1;

逻辑解释 #

• 先通过WHERE条件筛出所有可能返回的记录：要么是和传入参数x匹配的目标记录，要么是表中唯一那条subject_id为null的兜底记录，不会捞出多余数据
• 排序逻辑利用布尔值排序规则：当存在subject_id = x的记录时，该条记录的subject_id IS NULL判断结果为0（false），null兜底记录的判断结果为1（true），升序排列时匹配到x的记录会排在最前面；如果x没有匹配记录，筛选结果里就只剩null的兜底记录
• 最后用LIMIT取第一条结果，刚好符合「有匹配返回匹配项，无匹配返回兜底null记录」的要求

Oracle适配写法 #

Oracle不支持LIMIT语法，可调整为如下写法：

SELECT * FROM subject 
WHERE subject_id = x OR subject_id IS NULL
ORDER BY CASE WHEN subject_id IS NULL THEN 1 ELSE 0 END
FETCH FIRST 1 ROW ONLY;

注意点 #

• SQL中null值判断必须使用IS NULL，写subject_id = NULL是无效语法，永远匹配不到记录
• 由于subject_id是唯一值，给该字段建立索引后，上述查询可以直接走索引定位数据，性能和原基础查询几乎没有差异，不会产生全表扫描。

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