Teradata SQL NULLIF expression with Example

teradata

Teradata SQL NULLIF expression is used for setting some specific value to NULL. It compares two values and if they match then returns NULL else returns the first value which is passed to it.

Let’s create a table which we will use here to demo NULLIF example.

CREATE VOLATILE TABLE TST_CASE
 (
 EMPID INTEGER,
 NAME VARCHAR(30),
 YEARS_OF_EXP SMALLINT,
 SALARY INTEGER
 )
 PRIMARY INDEX (EMPID)
 ON COMMIT PRESERVE ROWS;

Add some dummy data into the table.

 INSERT INTO TST_CASE VALUES(1,'WILLIAM',5,50000);
 INSERT INTO TST_CASE VALUES(2,'SARA',3,30000);
 INSERT INTO TST_CASE VALUES(3,'ROBIN',-1,40000);
 INSERT INTO TST_CASE VALUES(4,'TED',7,70000);
 INSERT INTO TST_CASE VALUES(5,'LILY',-1,20000);

Preview the data in the table.

SELECT * FROM TST_CASE;
EMPIDNAMEYEARS_OF_EXPSALARY
1WILLIAM550000
2SARA330000
3ROBIN-140000
4TED770000
5LILY-120000

Let’s apply NULLIF function to change any value which is “-1” to NULL. So in the below example the function will compare value of column “YEARS_OF_EXP” to “-1” and if both the values match then it will convert it to NULL.

SELECT EMPID,NAME,
NULLIF(YEARS_OF_EXP,-1) AS YEARS_OF_EXP
,SALARY FROM TST_CASE
ORDER BY EMPID;
EMPIDNAMEYEARS_OF_EXPSALARY
1WILLIAM550000
2SARA330000
3ROBINNULL40000
4TED770000
5LILYNULL20000

It is similar to having a case statement where you compare two values and if the values are same then return NULL else return the first value.

1 thought on “Teradata SQL NULLIF expression with Example”

  1. Used to handle divide by zero scenarios e.g.

    select x/nullif(y, 0) ….

    returns null if y is zero, rather than a divide by zero exception

Leave a Reply

Your email address will not be published. Required fields are marked *