SQL中使用Union查询时为无数据的表返回默认值的实现

现有三张包含公共列的表Table1、Table2、Table3，原本执行如下Union查询：

select 'Section','Table1',column1, column2, column3 from table1 where column>1 
union
select 'Section','Table2',column1, column2, column3 from table2 where column>3
union
select 'Section','Table3',column1, column2, column3 from table3 where column>2

现在需要实现：当某张表（比如Table2）没有符合条件的记录时，不跳过该行，而是返回预设的默认值，默认值示例语句为：

select 'Section','Table2',0 as column1, 0 as column2, 0 as column3 

期望输出结果如下：

Section   Table1   2  2022-06-12   abc 
Section   Table2   0  ''           '' 
Section   Table3   3  2022-07-22   Xyz

这里提供两种可行的实现方式：

方式一：虚拟表关联+COALESCE填充默认值 #

先构造一个包含所有目标表名的虚拟数据集，再分别关联每个表的过滤结果，最后用COALESCE函数把空值替换成预设默认值，确保每个表都能返回一行数据。

示例SQL（以MySQL为例，其他数据库语法可稍作调整）：

SELECT 
    'Section' AS col0,
    t.table_name AS col1,
    COALESCE(
        CASE t.table_name 
            WHEN 'Table1' THEN t1.column1 
            WHEN 'Table2' THEN t2.column1 
            WHEN 'Table3' THEN t3.column1 
        END, 0
    ) AS column1,
    COALESCE(
        CASE t.table_name 
            WHEN 'Table1' THEN t1.column2 
            WHEN 'Table2' THEN t2.column2 
            WHEN 'Table3' THEN t3.column2 
        END, ''
    ) AS column2,
    COALESCE(
        CASE t.table_name 
            WHEN 'Table1' THEN t1.column3 
            WHEN 'Table2' THEN t2.column3 
            WHEN 'Table3' THEN t3.column3 
        END, ''
    ) AS column3
FROM (
    SELECT 'Table1' AS table_name UNION ALL
    SELECT 'Table2' AS table_name UNION ALL
    SELECT 'Table3' AS table_name
) t
LEFT JOIN (SELECT column1, column2, column3 FROM table1 WHERE column > 1) t1 ON t.table_name = 'Table1'
LEFT JOIN (SELECT column1, column2, column3 FROM table2 WHERE column > 3) t2 ON t.table_name = 'Table2'
LEFT JOIN (SELECT column1, column2, column3 FROM table3 WHERE column > 2) t3 ON t.table_name = 'Table3'

方式二：单表查询+NOT EXISTS判断默认值 #

针对每个表，先查询符合条件的记录，再用UNION ALL拼接默认值语句，通过NOT EXISTS判断该表是否有符合条件的记录，没有则返回默认值。

示例SQL：

-- 处理Table1
SELECT 'Section', 'Table1', column1, column2, column3 FROM table1 WHERE column > 1
UNION ALL
SELECT 'Section', 'Table1', 0, '', '' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM table1 WHERE column > 1)

UNION ALL

-- 处理Table2
SELECT 'Section', 'Table2', column1, column2, column3 FROM table2 WHERE column > 3
UNION ALL
SELECT 'Section', 'Table2', 0, '', '' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE column > 3)

UNION ALL

-- 处理Table3
SELECT 'Section', 'Table3', column1, column2, column3 FROM table3 WHERE column > 2
UNION ALL
SELECT 'Section', 'Table3', 0, '', '' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM table3 WHERE column > 2)

注：DUAL是Oracle、MySQL中的虚拟表，SQL Server可替换为(SELECT 1) AS DUAL，PostgreSQL可直接省略FROM DUAL。

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