Teradata SQL NULLIF expression with Example

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;

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;

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.