MySQL NULL: The Beginner’s Guide

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 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;

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 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 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 function accepts two arguments. If the two arguments are equal, the NULLIF function returns NULL. Otherwise, it returns the first argument.

The NULLIF function is useful when you have both NULL and empty string values in a column. For example, by mistake, you insert a 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;

  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

Managing Hierarchical Data in MySQL Using the Adjacency List Model

Hierarchical data is everywhere. It can be blog categories, product hierarchies, or organizational structures. There are many ways to manage hierarchical data in MySQL and the adjacency list model...

MySQL Row Count: How to Get Row Count in MySQL

Getting MySQL row count of a single table To get the row count of a single table, you use the COUNT(*) in a SELECT statement as follows: SELECT COUNT(*) FROM table_name; For example,...

MySQL Insert

Introduction to the MySQL INSERT statement The INSERT statement allows you to insert one or more rows into a table. The following illustrates the syntax of the INSERT statement: INSERT INTO...

MySQL DELETE

Introduction to MySQL DELETE statement To delete data from a table, you use the MySQL DELETE statement. The following illustrates the syntax of the DELETE statement: DELETE FROM table_name WHERE...

MySQL Compare Two Tables

In data migration, we often have to compare two tables to identify a record in one table that have no corresponding record in another table. For example, we have a new database whose schema is...