MySQL NULL Value Handling

We already know that MySQL usesSELECTcommand andWHEREclause to read data in the data table, but when the provided query condition field is NULL, the command may not work properly.

In MySQL, NULL is used to represent missing or unknown data. Handling NULL values requires special care because in the database it may cause results different from what you expect.

To handle this situation, MySQL provides three main operators:

• IS NULL:When the column value is NULL, this operator returns true.
• IS NOT NULL:When the column value is not NULL, the operator returns true.
• <=>:Comparison operator (unlike the = operator), which returns true when the two compared values are equal or when both are NULL.

The conditional comparison operation for NULL is special. You cannot use = NULL or != NULL to find NULL values in a column.

In MySQL, comparing NULL with any other value (even NULL) always returns NULL, that is, NULL = NULL returns NULL.

In MySQL, use the IS NULL and IS NOT NULL operators to handle NULL.

Note:

select * , columnName1+ifnull(columnName2,0) from tableName;

columnName1 and columnName2 are of type int. When columnName2 has a NULL value, columnName1 + columnName2 = NULL. ifnull(columnName2,0) converts the NULL value in columnName2 to 0.

Common considerations and tips for handling NULL values in MySQL

1. Check whether it is NULL:

To check whether a column is NULL, you can use the IS NULL or IS NOT NULL conditions.

SELECT * FROM employees WHERE department_id IS NULL;
SELECT * FROM employees WHERE department_id IS NOT NULL;

2. Use the COALESCE function to handle NULL:

The COALESCE function can be used to replace NULL values. It accepts multiple arguments and returns the first non-NULL value in the argument list:

SELECT product_name, COALESCE(stock_quantity, 0) AS actual_quantity
FROM products;

In the above SQL statement, if the stock_quantity column is NULL, COALESCE will return 0.

3. Use the IFNULL function to handle NULL:

The IFNULL function is MySQL's specific version of COALESCE. It accepts two arguments; if the first argument is NULL, it returns the second argument.

SELECT product_name, IFNULL(stock_quantity, 0) AS actual_quantity
FROM products;

4. NULL sorting:

When sorting with the ORDER BY clause, NULL values are placed at the end of the sort by default. If you want to place NULL values first, use ORDER BY column_name ASC NULLS FIRST; conversely, use ORDER BY column_name DESC NULLS LAST.

SELECT product_name, price
FROM products
ORDER BY price ASC NULLS FIRST;

5. Use the <=> operator for NULL comparison:

The <=> operator is a special operator in MySQL used to compare whether two expressions are equal. It also returns TRUE for comparisons involving NULL values. It can be used for equality comparisons with NULL values.

SELECT * FROM employees WHERE commission <=> NULL;

6. Note how aggregate functions handle NULL:

When using aggregate functions (such as COUNT, SUM, AVG), they ignore NULL values, which may lead to unexpected results. If you want to treat NULL as 0, you can use COALESCE or IFNULL.

SELECT AVG(COALESCE(salary, 0)) AS avg_salary FROM employees;

This way, even if salary is NULL, the aggregate function will treat it as 0.

When handling NULL values, be particularly careful to ensure that the semantics of queries and operations match expectations. When designing table structures, you also need to consider the usage scenarios and appropriateness of NULL values.

---

Using NULL values in the command prompt

In the following example, assume that the table example_test_tbl in the database EXAMPLE has two columns, example_author and example_count, and NULL values are inserted into example_count.

Example

Try the following example:

In the following example you can see that the = and != operators are not effective:

To find whether the example_test_tbl column in the data table is NULL, you must useIS NULLandIS NOT NULL, as in the following example:

---

Using PHP scripts to handle NULL values

In PHP scripts, you can use if...else statements to determine whether a variable is empty, and generate corresponding conditional statements.

In the following example, PHP sets the $example_count variable, and then uses that variable to compare with the example_count field in the data table:

Other extensions