Oracle多表优先级取数问题：按顺序获取非空DATE值

要实现「优先取高优先级表的非空DATE，无结果则依次往下取」的逻辑，可通过以下两种方式实现：

方式一：取符合条件的所有记录 #

如果需要获取高优先级表中所有非空DATE记录（而非单个值），可以用UNION ALL配合NOT EXISTS条件控制查询执行顺序：

-- 优先取TABLE1的非空DATE
SELECT DATE FROM TABLE1 WHERE DATE IS NOT NULL
UNION ALL
-- 仅当TABLE1无有效结果时，取TABLE2的非空DATE
SELECT DATE FROM TABLE2 
WHERE DATE IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM TABLE1 WHERE DATE IS NOT NULL)
UNION ALL
-- 仅当TABLE1、TABLE2都无有效结果时，取TABLE3的非空DATE
SELECT DATE FROM TABLE3 
WHERE DATE IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM TABLE1 WHERE DATE IS NOT NULL)
AND NOT EXISTS (SELECT 1 FROM TABLE2 WHERE DATE IS NOT NULL)
UNION ALL
-- 仅当TABLE1、TABLE2、TABLE3都无有效结果时，取TABLE4的非空DATE
SELECT DATE FROM TABLE4 
WHERE DATE IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM TABLE1 WHERE DATE IS NOT NULL)
AND NOT EXISTS (SELECT 1 FROM TABLE2 WHERE DATE IS NOT NULL)
AND NOT EXISTS (SELECT 1 FROM TABLE3 WHERE DATE IS NOT NULL)

逻辑说明：

• 高优先级表的查询先执行，若返回结果，后续低优先级表的NOT EXISTS条件会不成立，不会返回数据
• 只有当前面所有高优先级表都没有非空DATE时，才会执行当前表的查询

方式二：取单个非空DATE值 #

如果只需要获取第一个非空的DATE值（任意一条即可），可以用COALESCE函数结合子查询实现，不同数据库写法略有差异：

MySQL/PostgreSQL版本 #

SELECT COALESCE(
    (SELECT DATE FROM TABLE1 WHERE DATE IS NOT NULL LIMIT 1),
    (SELECT DATE FROM TABLE2 WHERE DATE IS NOT NULL LIMIT 1),
    (SELECT DATE FROM TABLE3 WHERE DATE IS NOT NULL LIMIT 1),
    (SELECT DATE FROM TABLE4 WHERE DATE IS NOT NULL LIMIT 1)
) AS target_date;

SQL Server版本 #

SELECT COALESCE(
    (SELECT TOP 1 DATE FROM TABLE1 WHERE DATE IS NOT NULL),
    (SELECT TOP 1 DATE FROM TABLE2 WHERE DATE IS NOT NULL),
    (SELECT TOP 1 DATE FROM TABLE3 WHERE DATE IS NOT NULL),
    (SELECT TOP 1 DATE FROM TABLE4 WHERE DATE IS NOT NULL)
) AS target_date;

Oracle版本 #

SELECT COALESCE(
    (SELECT DATE FROM TABLE1 WHERE DATE IS NOT NULL AND ROWNUM <= 1),
    (SELECT DATE FROM TABLE2 WHERE DATE IS NOT NULL AND ROWNUM <= 1),
    (SELECT DATE FROM TABLE3 WHERE DATE IS NOT NULL AND ROWNUM <= 1),
    (SELECT DATE FROM TABLE4 WHERE DATE IS NOT NULL AND ROWNUM <= 1)
) AS target_date FROM DUAL;

逻辑说明：

• COALESCE会返回第一个非空的子查询结果，一旦高优先级子查询返回非空值，后续子查询不会执行

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