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 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 NULLvalues first.

The following statement sorts the leads by phone number in ascending order.

SELECT
    *
FROM
    leads
ORDER BY phone;Code language: SQL (Structured Query Language) (sql)

In case you use the ORDER BY DESC, the NULL values appear at last of the result set. See the following example:

SELECT
    *
FROM
    leads
ORDER BY phone DESC;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

You can use the IS NOT operator to get all leads who provided the email addresses.

SELECT
    *
FROM
    leads
WHERE
    email IS NOT NULL;Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

The query returns only two rows because the rows whose email column is NULL are grouped into one.

Handling NULL Values in MySQL: IFNULL, COALESCE, and NULLIF

When working with databases, handling NULL values effectively is crucial to ensure accurate and meaningful query results. MySQL provides several built-in functions to manage NULL values efficiently. This guide will cover three key functions:

• IFNULL: Replaces NULL with a specified default value.
• COALESCE: Returns the first non-NULL value from a list of arguments.
• NULLIF: Returns NULL if two values are equal; otherwise, it returns the first value.

Summary

These functions allow for cleaner and more reliable data retrieval, preventing NULL values from causing issues in reporting and application logic.