Title: combine nvl into single statement
Tags: sql;oracle;join;left-join;nvl
Question: 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!
Comment: Please provide enough code so others can better understand or reproduce the problem.


Here is another answer: 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.

Here is another answer: 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

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 2, 'CommentCount': 1, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2021:00:09/23/21', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<sql><oracle><join><left-join><nvl>', 'Title': 'combine nvl into single statement', 'ViewCount': 59, 'id': 69294652, 'user_AboutMe': None, 'user_AccountId': 22613998.0, 'user_DisplayName': 'Eric', 'user_Reputation': 35.0, 'user_Views': 3.0, 'user_id': 16793735.0}}