Summary of knowledge points related to null (IFNULL, COALESCE and NULLIF) in MySQL

This article uses examples to explain the knowledge points related to null (IFNULL, COALESCE and NULLIF) in MySQL. Share with you for your reference, the details are as follows:

In MySQL, the NULL value represents an unknown value that is different from 0 or the empty string '' and is not equal to itself.

If we compare a NULL value with another NULL value or any other value, the result is NULL, because an unknown value (NULL value) is compared with another unknown value (NULL value), and its value is of course also an unknown value (NULL value).

However, we usually use NULL values ​​to indicate that data is missing, unknown, or inapplicable. For example, a prospect's phone number might be NULL and can be added later. So when we create a table, we can specify whether the column accepts NULL values ​​by using the NOT NULL constraint. Next, let's create a leads table and use it as a basis for a detailed understanding:

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

We can see that id is the primary key column, which does not accept any NULL values, and first_name, last_name, and source columns use the NOT NULL constraint, so no NULL values ​​can be inserted into these columns, while email and phone columns can accept NULL values.

So, we can use NULL value in insert statement to specify data missing. For example, the following statement inserts a row into the leads table. Because the phone number is missing, a NULL value is used:

INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('John','Doe','Web Search','[email protected]',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');

If we want to set the value of a column to NULL, we can use the assignment operator (=). For example, to update David William's phone to NULL, use the following UPDATE statement:

UPDATE leads 
SET 
  phone = NULL
WHERE
  id = 3;

But if you sort the result set in ascending order using the order by clause, MySQL considers NULL values ​​lower than other values, and therefore, it displays NULL values ​​first. The following query sorts the phone numbers in ascending order:

SELECT 
  *
FROM
  leads
ORDER BY phone;

Execute the above query statement, the results are as follows:

+----+------------+-----------+--------------+---------------------+----------------+
| id | first_name | last_name | source | email | phone |
+----+------------+-----------+--------------+---------------------+----------------+
| 1 | John | Doe | Web Search | [email protected] | NULL |
| 3 | David | William | Web Search | NULL | NULL |
| 2 | Lily | Bush | Cold Calling | NULL | (408)-555-1234 |
+----+------------+-----------+--------------+---------------------+----------------+

If you use ORDER BY DESC, NULL values ​​will appear at the end of the result set:

SELECT 
  *
FROM
  leads
ORDER BY phone DESC;

Execute the above query statement, the results are as follows:

+----+------------+-----------+--------------+---------------------+----------------+
| id | first_name | last_name | source | email | phone |
+----+------------+-----------+--------------+---------------------+----------------+
| 2 | Lily | Bush | Cold Calling | NULL | (408)-555-1234 |
| 1 | John | Doe | Web Search | [email protected] | NULL |
| 3 | David | William | Web Search | NULL | NULL |
+----+------------+-----------+--------------+---------------------+----------------+
3 rows in set

If we want to test for NULL in a query, we can use the IS NULL or IS NOT NULL operator in the where clause. For example, to get the leads who haven't provided a phone number, use the IS NULL operator as follows:

SELECT 
  *
FROM
  leads
WHERE
  phone IS NULL;

Execute the above query statement, the results are as follows:

+----+------------+-----------+------------+---------------------+-------+
| id | first_name | last_name | source | email | phone |
+----+------------+-----------+------------+---------------------+-------+
| 1 | John | Doe | Web Search | [email protected] | NULL |
| 3 | David | William | Web Search | NULL | NULL |
+----+------------+-----------+------------+---------------------+-------+
2 rows in set

We can also use the IS NOT operator to get all the leads who provided an email address:

SELECT 
  *
FROM
  leads
WHERE
  email IS NOT NULL;

Execute the above query statement, the results are as follows:

+----+------------+-----------+------------+---------------------+-------+
| id | first_name | last_name | source | email | phone |
+----+------------+-----------+------------+---------------------+-------+
| 1 | John | Doe | Web Search | [email protected] | NULL |
+----+------------+-----------+------------+---------------------+-------+
1 row in set

However, even if NULL is not equal to NULL, two NULL values ​​are considered equal in the GROUP BY clause. Let's take a look at the SQL example:

SELECT 
  email, count(*)
FROM
  leads
GROUP BY email;

This query returns only two rows because the rows whose email column is NULL are grouped into one row. The result is as follows:

+---------------------+----------+
| email | count(*) |
+---------------------+----------+
| NULL | 2 |
| [email protected] | 1 |
+---------------------+----------+
2 rows in set

We need to know that when you use a unique constraint or a UNIQUE index on a column, you can insert multiple NULL values ​​into that column. In this case, MySQL considers the NULL values ​​to be distinct. Next we verify this by creating a UNIQUE index on the phone column:

CREATE UNIQUE INDEX idx_phone ON leads(phone);

Here we should note that if we use the BDB storage engine, MySQL will consider NULL values ​​to be equal, so we cannot insert multiple NULL values ​​into a column with a unique constraint.

Now that we know the advantages and disadvantages of null, let's take a look at how to handle it in MySQL. MySQL provides a total of three functions, namely IFNULL, COALESCE and NULLIF.

Let's look at each one separately. First, 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 (phone) if it is not NULL, otherwise it returns N/A, not NULL. Let’s look at an example:

SELECT 
  id, first_name, last_name, IFNULL(phone, 'N/A') phone
FROM
  leads;

Execute the above query statement and get the following results:

+----+------------+-----------+----------------+
| id | first_name | last_name | phone |
+----+------------+-----------+----------------+
| 1 | John | Doe | N/A |
| 2 | Lily | Bush | (408)-555-1234 |
| 3 | David | William | N/A |
+----+------------+-----------+----------------+
3 rows in set

The COALESCE function takes 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 in the following order based on the priority of the information: phone, email, and N/A. Here are some examples:

SELECT 
  id,
  first_name,
  last_name,
  COALESCE(phone, email, 'N/A') contact
FROM
  leads;

Execute the above query statement and get the following code:

+----+------------+-----------+---------------------+
| id | first_name | last_name | contact |
+----+------------+-----------+---------------------+
| 1 | John | Doe | [email protected] |
| 2 | Lily | Bush | (408)-555-1234 |
| 3 | David | William | N/A |
+----+------------+-----------+---------------------+
3 rows in set

Finally, there is the NULLIF function, which accepts two parameters. 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, we mistakenly inserted the following row into the leads table:

INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('Thierry','Henry','Web Search','[email protected]','');

Because phone is an empty string: '', not NULL. So, if we want to get the contact information of a potential customer, we end up with a null phone number instead of an email address, like this:

SELECT 
  id,
  first_name,
  last_name,
  COALESCE(phone, email, 'N/A') contact
FROM
  leads;

Execute the above query statement and get the following code:

+----+------------+-----------+---------------------+
| id | first_name | last_name | contact |
+----+------------+-----------+---------------------+
| 1 | John | Doe | [email protected] |
| 2 | Lily | Bush | (408)-555-1234 |
| 3 | David | William | N/A |
| 4 | Thierry | Henry | |
+----+------------+-----------+---------------------+

If we want to solve this problem, we need to use the NULLIF function to compare the phone number with an 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;

Execute the above query statement and get the following code:

+----+------------+-----------+--------------------------+
| id | first_name | last_name | contact |
+----+------------+-----------+--------------------------+
| 1 | John | Doe | [email protected] |
| 2 | Lily | Bush | (408)-555-1234 |
| 3 | David | William | N/A |
| 4 | Thierry | Henry | [email protected] |
+----+------------+-----------+--------------------------+
4 rows in set

Okay, that’s all for this record.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.