coalesce in SQL

Question

I have a column called Indicator in a table. It contains Y, N, NULL, or just blank.

What does the following two logic do?

coalesce(Indicator, 'N') = 'N'
coalesce(Indicator, 'N') = 'Y'

It doesn't seem just to return the rows where Indicator equals N or Y. Is there something else going on?

Answer 1

For each condition there's a different answer

For

coalesce(Indicator, 'N') = 'N'

You get

coalesce('N', 'N') = 'N' --> 'N' = 'N' --> True
coalesce('Y', 'N') = 'N' --> 'Y' = 'N' --> False
coalesce(Null, 'N') = 'N' --> 'N' = 'N' --> True

and for

coalesce(Indicator, 'N') = 'Y'

you get

coalesce('N', 'N') = 'N' --> 'N' = 'N' --> True
coalesce('Y', 'N') = 'N' --> 'Y' = 'N' --> False
coalesce(Null, 'N') = 'Y' --> 'N' = 'Y' --> False

Answer 2

The logic does two things. Functionally, the first expression is equivalent to:

(Indicator = 'N' or Indicator is null)

In addition, it also prevents an index from being used on indicator (in most databases).

For a binary indicator, the use of the index is typically of minor importance. In addition, SQL optimizers are pretty bad at using indexes for or conditions. And, they almost never use them when a column is an argument to a function.

Answer 3

coalesce(Indicator, 'N') says if Indicator is null then take N as it's value else the value holded by Indicator.

so, if Indicator is null then the below condition holds TRUE

coalesce(Indicator, 'N') = 'N'