IFNULL Function

IFNULL

The IFNULL function is a logical function used to handle NULL values that may appear in SQL queries. When the value of the first parameter is NULL, the IFNULL function returns the value of the second parameter; if the value of the first parameter is not NULL, it returns the value of the first parameter.

Syntax


IFNULL(expression_1, expression_2)

expression_1: The expression to be checked, usually a field name.
expression_2: The value to return when expression_1 is NULL.

Instructions

The data types of expression_1 and expression_2 must be the same or compatible.
If the data types of expression_1 and expression_2 are not compatible, the IFNULL function will return NULL.
The return value of the IFNULL function is the data type of expression_1 or expression_2.

Example

Assume there is a table named student with the following columns:

id: The unique identifier of the student.
name: The name of the student.
score: The score of the student.

The data in the student table is as follows:

id	name	score
1	Alice	90
2	Bob	NULL
3	Cathy	80
4	David	NULL

Now, we want to query the name and score of each student, replacing records with NULL scores with 0. We can use the following SQL statement:


SELECT name, IFNULL(score, 0) AS score FROM student;

The query results are as follows:

name	score
Alice	90
Bob	0
Cathy	80
David	0

More Examples

Query the student's name and age. If the age is NULL, display it as "Unknown":


SELECT name, IFNULL(age, 'Unknown') AS age FROM student;

Query the employee's name and salary. If the salary is NULL, display it as "Not available":


SELECT name, IFNULL(salary, 'Not available') AS salary FROM employee;

Query the product name and inventory quantity. If the inventory quantity is NULL, display as "Out of stock":


SELECT product_name, IFNULL(stock_quantity, 'Out of stock') AS stock FROM products;