NULLIF Function - SQL

Overview

The NULLIF function in SQL returns null if two expressions are equal; otherwise, it returns the first expression. It is useful for handling specific cases where you want to substitute null for certain values.

Example:

SELECT NULLIF(department_id, 1)
FROM company.employees

Syntax:

SELECT NULLIF(expression1, expression2)
FROM table_name

expression1 is the first expression to evaluate.

expression2 is the second expression to compare with the first.

If expression1 equals expression2, the function returns null. If they are not equal, it returns expression1.

Sample Data:

    
            department_id
            first_name
        
            3
            Frank
        
            2
            Jane
        
            3
            Ashley
        
            NULL
            Glenn
        
            2
            Kelly
        
            1
            Richard
        
            1
            George
        
            5
            Kyle
        
            2
            James
        
            1
            Gustavo

department_id	first_name
3	Frank
2	Jane
3	Ashley
NULL	Glenn
2	Kelly
1	Richard
1	George
5	Kyle
2	James
1	Gustavo

Example: Query without the WHERE Statement

In this example, we are returning the department_id column and we use the NULLIF Function in another column to return NULL if the department_id is 1, otherwise it returns the actual department_id value.