How to Use the IFNULL() Function in MySQL

08/05/2021

Contents

In this article, you will learn how to use the IFNULL() function in MySQL.

Using the IFNULL() function in MySQL

The IFNULL() function in MySQL is used to return a specified value if a given expression is null.

Syntax

The syntax for using the IFNULL() function in MySQL is as follows:

SELECT IFNULL(expression, value_if_null) FROM table_name;

Where “expression” is the expression to be evaluated and “value_if_null” is the value to be returned if the expression is null.

Examples

Suppose we have a table named “students” with columns “student_id”, “first_name”, “last_name”, and “gpa”. We can use the IFNULL() function to replace any null values in the “gpa” column with a default value of 0.0 as follows:

SELECT student_id, first_name, last_name, IFNULL(gpa, 0.0) AS gpa FROM students;

This query will return the student ID, first name, last name, and GPA (with null values replaced by 0.0) of all students in the “students” table.

We can also use the IFNULL() function in combination with the WHERE clause to filter out rows with null values. For example:

SELECT student_id, first_name, last_name, gpa FROM students WHERE IFNULL(gpa, 0.0) >= 3.0;

This query will return the student ID, first name, last name, and GPA of all students in the “students” table who have a GPA of 3.0 or higher (with null values replaced by 0.0).

#MySQL