EDUCBA

Home Data Science Data Science Tutorials MySQL Tutorial MySQL IFNULL()

MySQL IFNULL()

MySQL IFNULL()

Introduction to MySQL IFNULL() function

MySQL IFNULL() function is a control flow function which 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 the alternate expression you desire, then this can be done using IFNULL () function.

The return value of the IFNULL () function can be either string or a numeric value. It basically 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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

In this session let us learn about the IFNULL function and examples of how it is used 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 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 - - - * /

Here if we check the above statement we can see that the ‘expression_1’ is NOT NULL. So, the output will be the ‘expression_1’. Screenshot is for the same:

Output:

MySQL IFNULL() 1

SELECT IFNULL(NULL,'HIII')AS Expression; / * - - - IFNULL FUNCTION - - - * /

Here if we check the above statement we can see that the ‘expression_1’ is NULL. So, the output will be the ‘alternate_expression’.

Output:

MySQL IFNULL() 2

SELECT IFNULL('','HIII')AS Expression; / * - - - IFNULL FUNCTION - - - * /

Here if we check the above statement we can see that the ‘expression_1’ is NOT NULL. As the empty string is also considered as value. So, the output will be the ‘alternate_expression’. Screenshot is for the same: –

Output:

MySQL IFNULL() 3

Examples of MySQL IFNULL()

The IFNULL() can be applied in 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 select query in the above table. We get below output:

MySQL IFNULL() 4

Now let us perform the IFNULL () function operates on the “Person_Phoneno” column and “Person_Landline” column. We will try to get value in 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 could see the output as follow:

MySQL IFNULL() 5

If we observe the above output we could see that the “Person_landline” value has been displayed whenever we have NULL value in the “Person_phoneno”.

If both the values are NULL we get output as NULL value itself. Below is the example for the same:

SELECT IFNULL(NULL, NULL)AS Expression; / * - - - IFNULL FUNCTION - - - * /

Here if we check the above statement we can see that the ‘expression_1’ is NULL and the alternate_function is also NULL as well. So, the output will be NULL.

Output:

MySQL IFNULL() 6

Example #2

Now let us consider another example for IFNULL () function.

SELECT IFNULL('EXPRESSION_1','HIII')AS Expression; / * - - - IFNULL FUNCTION - -* /

Here if we check the above statement we can see that the ‘expression_1’ is NOT NULL. So, the output will be the ‘expression_1’. Screenshot is for the same:

Output:

example 2

SELECT IFNULL(NULL,'Alternate expression')AS Expression; / * - - - IFNULL FUNCTION - -* /

Here 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:

example 2-1

Recommended Articles

This is a guide to MySQL IFNULL(). Here we discuss the Introduction, How does IFNULL() function works? and examples with code implementation respectively. You may also have a look at the following articles to learn more –

  1. MySQL having
  2. MySQL BLOB
  3. MySQL encode()
  4. MySQL today()