COALESCE Function - SQL

Overview

The COALESCE function in SQL is used to return the first non-null expression among its arguments. It is useful for handling null values in queries by substituting them with a default value or a non-null alternative.

Example:

SELECT COALESCE(10/0, 100)

Syntax:

SELECT COALESCE(expression1, expression2, ...expressionN)

expression1, expression2, ...expressionN: These are the expressions or values to evaluate. COALESCE returns the first non-null expression from the list.

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 use the COALESCE function to handle potential errors or NULL values. Since 10/0 causes a division by zero error, the COALESCE function returns the next non-null value, which is 100.

Example: Query without the WHERE Statement

In this example, we use the COALESCE function to return the department_id value if it is not NULL, otherwise it returns 10. You can see in the photo that the NULL value is replaced by a 10.