MySQL IFNULL
Summary: in this tutorial, you will learn about the MySQL IFNULL function, which is a very handy control flow function to handle NULL values.
Introduction to MySQL IFNULL function
MySQL IFNULL
the function is one of the MySQL control flow functions that accepts two arguments and returns the first argument if it is not NULL
. Otherwise, the IFNULL
function returns the second argument.
The two arguments can be literal values or expressions.
The following illustrates the syntax of the IFNULL
function:
IFNULL(expression_1,expression_2);
The IFNULL
function returns expression_1
if expression_1
is not NULL
; otherwise, it returns expression_2
. The
The IFNULL
the function returns a string or a numeric based on the context where it is used.
If you want to return a value based on TRUE
or FALSE
condition other than NULL
, you should use the IF function.
MySQL IFNULL function examples
See the following IFNULL
function examples:
SELECT IFNULL(1,0); -- returns 1
SELECT IFNULL('',1); -- returns ''
SELECT IFNULL(NULL,'IFNULL function'); -- returns IFNULL function
How it works.
IFNULL(1,0)
returns 1 because 1 is notNULL
.IFNULL(' ',1)
returns' '
because the' '
string is notNULL
.IFNULL(NULL,'IFNULL function')
returnsIFNULL function
string because the first argument isNULL
.
Let’s take a practical example of using the IFNULL
function.
First, create a new table named contacts
using the following statement:
CREATE TABLE IF NOT EXISTS contacts (
contactid INT AUTO_INCREMENT PRIMARY KEY,
contactname VARCHAR(20) NOT NULL,
bizphone VARCHAR(15),
homephone VARCHAR(15)
);
Each contact has a name, business phone, and home phone.
Second, insert data into the contacts
table:
INSERT INTO contacts(contactname,bizphone,homephone)
VALUES('John Doe','(541) 754-3009',NULL),
('Cindy Smith',NULL,'(541) 754-3110'),
('Sue Greenspan','(541) 754-3010','(541) 754-3011'),
('Lily Bush',NULL,'(541) 754-3111');
Some contacts have only a home phone or business phone. To get all the contact names and phone from the contacts
table, you use the following query:
SELECT
contactName, bizphone, homephone
FROM
contacts;
It would be nice if we can get the contact’s home phone if the contact’s business phone is not available. This is where the
This is where the IFNULL
function comes to play. The IFNULL
function returns the home phone if the business phone is NULL
.
Third, use the following query to get the names and phones of all the contacts:
SELECT
contactname, IFNULL(bizphone, homephone) phone
FROM
contacts;
Notice that you should avoid using the IFNULL
function in the WHERE clause, because it degrades the performance of the query. If you want to check if a value is
If you want to check if a value is NULL
or not, you can use IS NULL
or IS NOT NULL
in the WHERE
clause.
In this tutorial, we have introduced you to MySQL IFNULL
function and show you how to use the IFNULL
function in the queries.
0 Comments
CAN FEEDBACK
Emoji