MySQL NULL: The Beginner’s Guide
Summary: in this tutorial, you will learn how to work with MySQL NULL
values. In addition, you’ll learn some useful functions to deal with the NULL
values effectively.
Introduction to MySQL NULL
values
In MySQL, a NULL
value means unknown. A NULL
value is different from zero (0
) or an empty string ''
.
A NULL
value is not equal to anything, even itself. If you compare a NULL
value with another NULL
value or any other value, the result is NULL
because the value of each NULL
value is unknown.
Generally, you use the NULL
value to indicate that the data is missing, unknown, or not applicable. For example, the phone number of a potential customer may be NULL
and can be added later.
When you create a table, you can specify whether a column accepts NULL
values or not by using the NOT NULL
constraint.
For example, the following statement creates the leads
table:
DROP TABLE IF EXISTS leads;
CREATE TABLE leads (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
source VARCHAR(255) NOT NULL,
email VARCHAR(100),
phone VARCHAR(25)
);
In this leads
table, the column id
is the primary key column, therefore, it does not accept any NULL
value.
The first_name
, last_name
, and source
columns use the NOT NULL
constraints, hence, you cannot insert any NULL
values into these columns, whereas the email
and phone
columns accept NULL values.
You can use a NULL
value in the INSERT
statement to specify that the data is missing. For example, the following statement inserts a row into the leads
table. Because the phone number is missing, so a NULL
value is used.
INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('John','Doe','Web Search','john.doe@acme.com',NULL);
Because the default value of the email column is NULL
, you can omit the email in the INSERT
a statement as follows:
INSERT INTO leads(first_name,last_name,source,phone)
VALUES
('Lily','Bush','Cold Calling','(408)-555-1234'),
('David','William','Web Search','(408)-888-6789');
MySQL SET NULL
in UPDATE
statement
To set the value of a column to NULL
, you use the assignment operator ( =
). For example, to update the phone of David William
to NULL
, you use the following UPDATE
statement:
UPDATE leads
SET
phone = NULL
WHERE
id = 3;
MySQL ORDER BY
with NULL
If you use the ORDER BY
clause to sort the result set in the ascending order, MySQL considers NULL
values are lower than other values, therefore, it presents the NULL
values first.
The following statement sorts the leads by phone number in ascending order.
SELECT
*
FROM
leads
ORDER BY phone;
In case you use the ORDER BY DESC
, the NULL
values appear at the last of the result set. See the following example:
SELECT
*
FROM
leads
ORDER BY phone DESC;
To test for NULL
in a query, you use the IS NULL
or IS NOT NULL
operator in the WHERE
clause.
For example, to get the leads who have not yet provided the phone number, you use the IS NULL
operator as follows:
SELECT
*
FROM
leads
WHERE
phone IS NULL;
You can use the IS NOT
operator to get all leads who provided the email addresses.
SELECT
*
FROM
leads
WHERE
email IS NOT NULL;
Even though the NULL
is not equal to NULL
, two NULL
values are equal in the GROUP BY
clause.
SELECT
id, first_name, last_name, email, phone
FROM
leads
GROUP BY email;
The query returns only two rows because the rows whose email column is NULL
are grouped into one.
MySQL NULL
and UNIQUE
index
When you use a UNIQUE constraint or UNIQUE index on a column, you can insert multiple NULL
values into that column. It is perfectly fine because in this case, MySQL considers NULL
values are distinct.
Let’s verify this point by creating a UNIQUE
index for the phone
column.
CREATE UNIQUE INDEX idx_phone ON leads(phone);
Notice that if you use the BDB storage engine, MySQL considers the NULL
values are equal therefore you cannot insert multiple NULL
values into a column that has a unique constraint.
MySQL NULL
functions
MySQL provides several useful functions that handle NULL effectively: IFNULL
, COALESCE
, and NULLIF
.
The IFNULL
the function accepts two parameters. The IFNULL
function returns the first argument if it is not NULL
, otherwise, it returns the second argument.
For example, the following statement returns the phone number if it is not NULL
otherwise, it returns N/A
instead of NULL
.
SELECT
id,
first_name,
last_name,
IFNULL(phone, 'N/A') phone
FROM
leads;
The COALESCE
the function accepts a list of arguments and returns the first non-NULL argument. For example, you can use the COALESCE
function to display the contact information of a lead based on the priority of the information in the following order: phone, email, and N/A.
SELECT
id,
first_name,
last_name,
COALESCE(phone, email, 'N/A') contact
FROM
leads;
The NULLIF
the function accepts two arguments. If the two arguments are equal, the NULLIF
function returns NULL
. Otherwise, it returns the first argument.
The NULLIF
the function is useful when you have both NULL
an empty string values in a column. For example, by mistake, you insert the following row into the leads
table:
INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('Thierry','Henry','Web Search','thierry.henry@example.com','');
The phone is an empty string instead of NULL
.
If you want to get the contact information of leads, you end up with an empty phone instead of the email as the following query:
SELECT
id,
first_name,
last_name,
COALESCE(phone, email, 'N/A') contact
FROM
leads;
To fix this, you use the NULLIF
function to compare the phone with the empty string, if they are equal, it returns NULL
, otherwise, it returns the phone number.
SELECT
id,
first_name,
last_name,
COALESCE(NULLIF(phone, ''), email, 'N/A') contact
FROM
leads;
In this tutorial, you have learned how to work with MySQL NULL
and how to use some handy functions to handle NULL
in queries.
0 Comments
CAN FEEDBACK
Emoji