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

  • 2021-12-21 05:27:03
  • OfStack

In this paper, the relevant knowledge points of null (IFNULL, COALESCE and NULLIF) in mysql are described with examples. Share it for your reference, as follows:

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

If we compare the NULL value with another NULL value or any other value, the result is NULL, because a value that does not know what it is (NULL value) is compared with another value that does not know what it is (NULL value), and of course the value is also a value that does not know what it is (NULL value).

However, we usually use the NULL value to indicate data loss, unknown, or inapplicable. For example, the lead may have a phone number of 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 an leads table, and use it as a basis to know more about it:


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 a primary key column that does not accept any NULL values, and then the first_name, last_name, and source columns use the NOT NULL constraints, so no NULL values can be inserted into these columns, while the email and phone columns accept NULL values.

Therefore, we can use the NULL value in the insert statement to specify data loss. For example, the following statement inserts 1 row into the thread table. The NULL value is used because the phone number is missing:


INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('John','Doe','Web Search','john.doe@yiibai.com',NULL);

Because the default value of the email column is NULL, you can omit e-mail messages 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 the column to NULL, we can use the assignment operator (=). For example, to update the handset of David William (phone) to NULL, use the following UPDATE statement:


UPDATE leads 
SET 
  phone = NULL
WHERE
  id = 3;

However, if the result set is sorted in ascending order using the order by clause, MySQL assumes that the NULL value is lower than the other values, so it displays the NULL value first. The following query statements are arranged in ascending order by telephone number (phone):


SELECT 
  *
FROM
  leads
ORDER BY phone;

Execute the above query statement, and the result is as follows:


+----+------------+-----------+--------------+---------------------+----------------+
| id | first_name | last_name | source    | email        | phone     |
+----+------------+-----------+--------------+---------------------+----------------+
| 1 | John    | Doe    | Web Search  | john.doe@yiibai.com | NULL      |
| 3 | David   | William  | Web Search  | NULL        | NULL      |
| 2 | Lily    | Bush   | Cold Calling | NULL        | (408)-555-1234 |
+----+------------+-----------+--------------+---------------------+----------------+

If you use ORDER BY DESC, the NULL value is displayed at the end of the result set:


SELECT 
  *
FROM
  leads
ORDER BY phone DESC;

Execute the above query statement, and the result is as follows:


+----+------------+-----------+--------------+---------------------+----------------+
| id | first_name | last_name | source    | email        | phone     |
+----+------------+-----------+--------------+---------------------+----------------+
| 2 | Lily    | Bush   | Cold Calling | NULL        | (408)-555-1234 |
| 1 | John    | Doe    | Web Search  | john.doe@yiibai.com | NULL      |
| 3 | David   | William  | Web Search  | NULL        | NULL      |
+----+------------+-----------+--------------+---------------------+----------------+
3 rows in set

If we want to test NULL in a query, we can use the IS NULL or IS NOT NULL operator in the where clause. For example, to get a lead that has not provided a phone number, use the IS NULL operator, as follows:


SELECT 
  *
FROM
  leads
WHERE
  phone IS NULL;

Execute the above query statement, and the result is as follows:


+----+------------+-----------+------------+---------------------+-------+
| id | first_name | last_name | source   | email        | phone |
+----+------------+-----------+------------+---------------------+-------+
| 1 | John    | Doe    | Web Search | john.doe@yiibai.com | NULL |
| 3 | David   | William  | Web Search | NULL        | NULL |
+----+------------+-----------+------------+---------------------+-------+
2 rows in set

We can also use the IS NOT operator to get all leads that provide e-mail addresses:


INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('John','Doe','Web Search','john.doe@yiibai.com',NULL);

0

Execute the above query statement, and the result is as follows:


INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('John','Doe','Web Search','john.doe@yiibai.com',NULL);

1

However, even if NULL is not equal to NULL, in the GROUP BY clause, two NULL values are equal, so look at the sql instance:


INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('John','Doe','Web Search','john.doe@yiibai.com',NULL);

2

The query returns only two rows, because the rows whose mailbox (email) is listed as NULL are grouped into one row, and the result is as follows:


+---------------------+----------+
| email        | count(*) |
+---------------------+----------+
| NULL        |    2 |
| john.doe@yiibai.com |    1 |
+---------------------+----------+
2 rows in set

We need to know that when you use a 1-only constraint or an UNIQUE index on a column, you can insert multiple NULL values into that column, in which case MySQL assumes that the NULL values are different. Next we verify this point by creating an UNIQUE index for the phone column:


INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('John','Doe','Web Search','john.doe@yiibai.com',NULL);

4

Here we should note that mysql will assume that NULL values are equal if the BDB storage engine is used, so we cannot insert multiple NULL values into columns with only one constraint.

Now that we know the advantages and disadvantages of null, let's look at how to deal with it in mysql. mysql1 provides three functions, IFNULL, COALESCE, and NULLIF.

Let's look at it separately. First, the IFNULL function accepts two parameters. If the IFNULL function is not NULL, the first parameter is returned, otherwise the second parameter is returned. For example, if it is not NULL, the following statement returns the phone number (phone), otherwise it returns N/A instead of NULL. Take an example:


INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('John','Doe','Web Search','john.doe@yiibai.com',NULL);

5

Execute the above query statement and get the following results:


INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('John','Doe','Web Search','john.doe@yiibai.com',NULL);

6

The end result is the COALESCE function, which accepts the argument list and returns the first non-NULL argument. For example, you can use the COALESCE function to display contact information for threads in the following order according to the priority of information: phone, email, and N/A. Here's a case study:


INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('John','Doe','Web Search','john.doe@yiibai.com',NULL);

7

Execute the above query statement and get the following code:


+----+------------+-----------+---------------------+
| id | first_name | last_name | contact       |
+----+------------+-----------+---------------------+
| 1 | John    | Doe    | john.doe@yiibai.com |
| 2 | Lily    | Bush   | (408)-555-1234   |
| 3 | David   | William  | N/A         |
+----+------------+-----------+---------------------+
3 rows in set

Finally, the NULLIF function takes two parameters. If the two parameters are equal, the NULLIF function returns NULL. Otherwise, it returns the first parameter. The NULLIF function is useful when a column has both NULL and an empty string value. For example, we incorrectly inserted the following row into the leads table:


INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('John','Doe','Web Search','john.doe@yiibai.com',NULL);

9

Because phone is an empty string: '', not NULL. So, if we want to get contact information for a potential customer, we end up with an empty phone instead of an e-mail, as follows:


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    | john.doe@yiibai.com |
| 2 | Lily    | Bush   | (408)-555-1234   |
| 3 | David   | William  | N/A         |
| 4 | Thierry  | Henry   |           |
+----+------------+-----------+---------------------+

If we want to solve this problem, we will use the NULLIF function to compare the phone with an empty string (''). If it is equal, we will return NULL, otherwise, we will return 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:


INSERT INTO leads(first_name,last_name,source,phone)
VALUES('Lily','Bush','Cold Calling','(408)-555-1234'),
('David','William','Web Search','(408)-888-6789');

3

Ok, that's all for this record.

For more readers interested in MySQL related content, please check the topics on this site: "MySQL Query Skills Encyclopedia", "MySQL Transaction Operation Skills Summary", "MySQL Stored Procedure Skills Encyclopedia", "MySQL Database Lock Related Skills Summary" and "MySQL Common Functions Summary"

I hope this article is helpful to everyone's MySQL database.


Related articles: