EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL IFNULL()
 

MySQL IFNULL()

Updated June 2, 2023

MySQL IFNULL()

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

MySQL IFNULL() 1

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:

MySQL IFNULL() 2

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:

MySQL IFNULL() 3

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:

Using Select Query 4

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:

MySQL IFNULL() 5

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:

MySQL IFNULL() 6

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:

example 2

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:

example 2-1

Recommended Articles

We hope this EDUCBA information on “MySQL IFNULL()” benefited you. You can view EDUCBA’s recommended articles for more information.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW