Union in sql with default return-CodePudding

I have scenarios where there are multiple tables Table1, Table2, and Table3 and there are some common columns in them. Now I have to take join with tables on bases of the condition if record exists from the table than its good but if it not exist then it doesn't return any row but I have to return some default/0

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

suppose data doesn't exist in table 2 instead of skipping that table record should show in the result in simple I want if the record not exist against any table it would be replaced by the below code

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

Output should be like this Results

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

CodePudding user response：

You can use EXISTS. ie:

select 'Section','Table1',column1, column2, column3 
from (values (0,0,0)) 
t(column1,column2,column3) 
where not exists (select * from table1 where column1 > 1)
union
select 'Section','Table1',column1, column2, column3 from table1 where column1>1 
union
select 'Section','Table2',column1, column2, column3 
from (values (0,0,0)) 
t(column1,column2,column3) 
where not exists (select * from table2 where column1 > 3)
union
select 'Section','Table2',column1, column2, column3 from table2 where column1>3
union
select 'Section','Table3',column1, column2, column3 
from (values (0,0,0)) 
t(column1,column2,column3) 
where not exists (select * from table3 where column1 > 2)
union
select 'Section','Table3',column1, column2, column3 from table3 where column1>2;

DBFiddle demo It is SQL server but is valid for many databases if not all.