IFNULL Function - SQL

Overview

The IFNULL function in SQL returns the first argument if it is not null; otherwise, it returns the second argument. It is useful for handling null values by substituting them with a default value.

Example:

SELECT IFNULL(10/0, 100)

Syntax:

SELECT IFNULL(expression, default value)

expression is the value to evaluate for null.

default_value is the value returned if expression is null.

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: Hard-coded value

In this example, we attempt to divide 10 by 0 which results in a NULL value due to a division by zero error. The IFNULL function then replaces the NULL value with 100 for a final output of 100.

Example: Query without the WHERE Statement

In this example, we are returning the department_id column and in another column we are checking to see if any values in the department_id column are NULL, if it is NULL, then it will return a value of 10, if it is not NULL then it will return the actual department_id value.