SQL: Is there a way for me to call another table.field when my initial table.field = NULL?

Question

Morning All! Essentially, I found out that a potential table.field I'd like to use may not have the information I want, so, I might have to look into a separate field. My below thought process is:

If table.field1 = NULL then pull the value from table.field2. If table.field2 = NULL then state "No Phone"

My current SQL statement in the select, cause it's in a certain format, is:

substring(view_episode_summary_current.patient_home_phone, 1, 3) || ' ' || substring(view_episode_summary_current.patient_home_phone, 5, 8)

Above is let's say table.field1. I'm assuming I'll need to create a CASE statement right? I just didn't know how long it could be?

(case when view_episode_summary_current.patient_home_phone = NULL then table.field2)

But I don't know how to get it to evaluate if table.field2 = null and display the value.

Answer 1

In many databases, you can use coalesce() to implement that kind of logic:

coalesce(t1.field1, t2.field2) as myfield

coalesce() returns the first non-null value across its arguments. So it gives you t1.field1 if it is not null, else it falls back on t2.field2. If both are null, you get a null value as a result.

You could also use a case expression - there is no benefit as compared to colaesce(), but this can handle more complex cases than just nullity checks

case
    when t1.field1 is not null then t1.field1
    when t2.field2 is not null then t2.field2
end as myfield

case stops on the first branch where the condition is fulfilled. If no branch matches, it returns null - you can put an else branch at the end of your code to return something else.

Note that both techniques requires both fields to have similar data types.

You do not show your actual query so I cannot show how to use that in your code.