combine nvl into single statement-CodePudding

There are two different tables

Table 1:

select nvl(a.column1,'dummy_employee') from table1 a;

Table 2:

select nvl(b.column1,a.column1) from table1 a
left join table2 b
on a.key_col=b.key_col

Logic:

If column1 from table1 is null then, return 'dummy_employee', else if key_col in both table1 and table2 are equal then return column1 from table2, else return column1 from table1

Aim is to combine both these logic into one, what would be the best way to combine above 2 nvl statements into one, thanks!

CodePudding user response：

One option would be using case

select case when a.column1 is null then 'dummy_employee' 
            when a.key_col=b.key_col then b.column1 
            else a.column1 
from table1 a
left join table2 b
on a.key_col=b.key_col

CodePudding user response：

I think you can do what you want using coalesce() and tweaking the query:

select coalesce(b.column1, a.column1, 'dummy employee')
from table1 a left join
     table2 b
     on a.key_col = b.key_col and a.column1 is not null;

This is not 100% equivalent to your query (for instance, if there are multiple matches in table2 where a.column1 is null, this returns only one row). But I suspect it does what you want.