SQL that also gives the table names from which the columns were derived

There is a need in the SQL code below will also get the table name from which the column was fetched to maintain a lineage for analysis at later point. I need suggestion to implement such a SQL:

select
COALESCE(t1.col1,t2.col1,t3.col1) new_col1,
COALESCE(t1.col2,t2.col2,t3.col2) new_col2,
COALESCE(t1.col3,t2.col3,t3.col3) new_col3
from
table1 t1 
left join table2 t2 on t1.id = t2.id
left join table3 t3 on t1.id = t3.id

In the result, I need to get an output similar to this:

new_col1 new_col2 new_col3 new_col1_source new_col2_source new_col3_source
val1     val2     val3     table1          table1          table3  

in the above result, the last 3 columns should provide the table names from which the first 3 columns were fetched from.

Answer

You can do this:

select
COALESCE(t1.col1,t2.col1,t3.col1) new_col1,
COALESCE(t1.col2,t2.col2,t3.col2) new_col2,
COALESCE(t1.col3,t2.col3,t3.col3) new_col3,
case when t1.col1 is not null then 'table1' 
     when t2.col1 is not null then 'table2'
     when t3.col1 is not null then 'table3' end as new_col1_source,
case when t1.col2 is not null then 'table1' 
     when t2.col2 is not null then 'table2'
     when t3.col2 is not null then 'table3' end as new_col2_source,
case when t1.col3 is not null then 'table1' 
     when t2.col3 is not null then 'table2'
     when t3.col3 is not null then 'table3' end as new_col3_source
from
table1 t1 
left join table2 t2 on t1.id = t2.id
left join table3 t3 on t1.id = t3.id

I’m not saying it’s elegant. On the contrary, combining data and metadata in a single query inevitably results in clunkiness.