ISNULL in a Case When

Question

I’m making a select using the CASE WHEN in Sql Server, so that the existence of a record is checked, if it exists, it selects in a table, otherwise it selects in another table, but the two can return null. Something more or less like this:

CASE WHEN EXISTS(SELECT Id FROM TabelaA WHERE ColA = 10)
    THEN
        (SELECT ColA from TabelaA WHERE ColA = 10)
    ELSE
        (SELECT ColA from TabelaB WHERE ColB = 10)
END Column

How to use a ISNULL in that script so as to avoid a null return of both selects?

Answer 1

I solved the problem simply, (I was surprised, I didn’t know it worked):

 ISNULL(CASE WHEN EXISTS(SELECT Id FROM TabelaA WHERE ColA = 10)
          THEN
             (SELECT ColA from TabelaA WHERE ColA = 10)
          ELSE
             (SELECT ColA from TabelaB WHERE ColB = 10)
        END, '') Column

Answer 2

Another simpler form of resolution would be using the COALESCE The advantage of coalesce is that you can provide a list of parameters and the first non-null is displayed

COALESCE(A,B,C,D..Z)

If A is null then rate B, if B is null then rate C, etc..

The code proposition eliminate the case structure:

Select Coalesce ((SELECT ColA from TabelaA WHERE ColA = 10), (SELECT ColA from TabelaB WHERE ColB = 10)) 

See an example working on SQL FIDDLE