COALESCE VS ZEROIFNULL VS CASE statement

Database
Enthusiast

COALESCE VS ZEROIFNULL VS CASE statement

Hi All,

I have a decimal column.
Whenever value is NULL in the column, I want to make it 0.
Which option is better? why?
1. COALESCE(col_name,0)
2. ZEROIFNULL(col_name)
3. CASE WHEN col_name IS NULL THEN 0 ELSE col_name

 

Thanks,

Sankalp

Tags (2)
7 REPLIES
rjg
Supporter

Re: COALESCE VS ZEROIFNULL VS CASE statement

Sankalp,

The result datatype will be the same as your decimal column for zeroifnull and coalesce but for case it will be byteint.

Other than that the performance will be similar.

 

 

Rglass

Senior Apprentice

Re: COALESCE VS ZEROIFNULL VS CASE statement

Why do you think that CASE differs? COLASCE is just a simplified CASE.

rjg
Supporter

Re: COALESCE VS ZEROIFNULL VS CASE statement

It differs in the way I indicated on 15.0

I consider a datatype change a difference. 

Senior Apprentice

Re: COALESCE VS ZEROIFNULL VS CASE statement

SELECT 1245.567 AS col_name
    ,TYPE( CASE WHEN col_name IS NULL THEN 0 ELSE col_name end)
    ,TYPE(Coalesce(col_name,0))
    ,TYPE(ZeroIfNull(col_name))

1.245,567	DECIMAL(7,3)	DECIMAL(7,3)	DECIMAL(7,3)

Teradata 15.10.02.06
rjg
Supporter

Re: COALESCE VS ZEROIFNULL VS CASE statement

Ok,

I missed the else clause.

 

SyntaxEditor Code Snippet

create volatile table nulltest (b char(1), a decimal(7,3)) on commit preserve rows;
insert into nulltest values('a',);
insert into nulltest values('b',9999.999);

SELECT 
     TYPE( CASE WHEN a IS NULL THEN 0 end)
    ,TYPE(Coalesce(a,0))
    ,TYPE(ZeroIfNull(a))From nulltest;


BYTEINT    DECIMAL(7,3)    DECIMAL(7,3)BYTEINT    DECIMAL(7,3)    DECIMAL(7,3)
Teradata Employee

Re: COALESCE VS ZEROIFNULL VS CASE statement

SELECT TYPE( CASE WHEN a IS NULL THEN 0000.000 end)

returns "Decimal(7,3)"

Enthusiast

Re: COALESCE VS ZEROIFNULL VS CASE statement

Result for CASE may be ByteInt but i,m to store the result(i.e., 0) in the same Decimal column(DECIMAL(18,2)), so it will take up the defined decimal space, right? That means, we can choose any as there is no performance benefit also of using one over the others.