Updated June 2, 2023
Introduction to MySQL IFNULL() function
MySQL IFNULL() function is a control flow function that consists of two arguments, expression_1, and alternate_expression. Here expression_1 is returned if the value is NOT NULL. Else, it returns the value of the alternate_expression. If you want to replace a NULL with your desired alternate expression, this can be done using IFNULL () function.
The return value of the IFNULL () function can be either string or a numeric value. It depends on the context. It’s good practice to avoid IFNULL() function in the WHERE clause as it will degrade the performance; instead, you can use ISNULL() or IS NOT NULL function.
This session let us learn about the IFNULL function and examples of its use in real-time scenarios.
Syntax:
Now let us see the syntax of the IFNULL () function: –
ifnull(expression_1, alternate_expression);In the above syntax, if the expression_1 is NULL, then the alternate_expression value will be returned. If the expression_1 is NOT NULL, then it returns the expression_1 value.
How does IFNULL() function work?
Now let us see how the IFNULL () works:
SELECT IFNULL('HI','HIII')AS Expression; / * - - - IFNULL FUNCTION - - - * /If we check the above statement, we can see that the ‘expression_1’ is NOT NULL. So, the output will be the ‘expression_1’. The screenshot is for the same:
Output:
SELECT IFNULL(NULL,'HIII')AS Expression; / * - - - IFNULL FUNCTION - - - * /If we check the above statement, we can see that the ‘expression_1’ is NULL. So, the output will be the ‘alternate_expression’.
Output:
SELECT IFNULL('','HIII')AS Expression; / * - - - IFNULL FUNCTION - - - * /If we check the above statement, we can see that the ‘expression_1’ is NOT NULL. As the empty string is also considered a value. So, the output will be the ‘alternate_expression’. The screenshot is for the same: –
Output:
Examples of MySQL IFNULL()
The IFNULL() can be applied at the table level as well:
Now let us create a table and apply the IFNULL () function on it:
CREATE TABLE IFNULL_TEST / * - - - Creating IFNULL_TEST table - - - * /
(
Person_FirstName Varchar(20)
,Person_LastName Varchar(20)
,Person_location Varchar(20)
,Person_phoneno Varchar(20)
,Person_Landline Varchar(20)
);Now let us insert data into the table: -/ * – – – Insert data into the table – – – * /
INSERT INTO IFNULL_TEST VALUES ('Bob','Miley','Norway',NULL,'(567)0772345');
INSERT INTO IFNULL_TEST VALUES ('Mark','Zucker','England','451667287','(567)0772345');
INSERT INTO IFNULL_TEST VALUES ('Fred','Bostan','Holloland',NULL,'(567)0772345');
INSERT INTO IFNULL_TEST VALUES ('Ben','Haminnton','Poland','716672870','(567)0772345');
INSERT INTO IFNULL_TEST VALUES ('William','Haminnton','Paris','231667287',NULL);
INSERT INTO IFNULL_TEST VALUES ('Sam','Miley','Norway',NULL,'(567)0772345');
INSERT INTO IFNULL_TEST VALUES ('Julie','Murray','Norway','29357683','(567)0772345');
INSERT INTO IFNULL_TEST VALUES ('Bobby','stuart','France',NULL,'(567)0772345');Let us perform a select query in the above table. We get the below output:
Now let us perform the IFNULL () function operates on the “Person_Phoneno” column and “Person_Landline” column. We will try to get a value such that if the “Person_Phoneno” IS NULL, then get the “Person_Landline” Value into the column. Below is the syntax for the same:
SELECT *,IFNULL(Person_Phoneno,Person_landline)As "Contact details" FROM IFNULL_TEST;
/ * - - - IFNULL FUNCTION - - - * /We can see the output as follow:
Observing the above output shows us that the “Person_landline” value has been displayed whenever we have a NULL value in the “Person_phoneno”.
If both the values are NULL, we get output as a NULL value. Below is an example of the same:
SELECT IFNULL(NULL, NULL)AS Expression; / * - - - IFNULL FUNCTION - - - * /If we check the above statement, we can see that the ‘expression_1’ is NULL and the alternate_function is also NULL. So, the output will be NULL.
Output:
Example #2
Now let us consider another example for IFNULL () function.
SELECT IFNULL('EXPRESSION_1','HIII')AS Expression; / * - - - IFNULL FUNCTION - -* /If we check the above statement, we can see that the ‘expression_1’ is NOT NULL. So, the output will be the ‘expression_1’. A screenshot is for the same:
Output:
SELECT IFNULL(NULL,'Alternate expression')AS Expression; / * - - - IFNULL FUNCTION - -* /If we check the above statement, we can see that the ‘expression_1’ is NOT NULL. So, the output will be the ‘Alternate expression’.
Output:
Recommended Articles
We hope this EDUCBA information on “MySQL IFNULL()” benefited you. You can view EDUCBA’s recommended articles for more information.
