MySQL NULL Value Handling

We already know that MySQL uses the SELECT command and the WHERE clause to read data from tables. However, when the query condition field provided is NULL, the command may not work as expected.

In MySQL, NULL is used to represent missing or unknown data. Handling NULL values requires special care, as it can lead to unexpected results in the database.

To handle this situation, MySQL provides three main operators:

IS NULL: Returns true when the column value is NULL.
IS NOT NULL: Returns true when the column value is not NULL.
<=>: A comparison operator (different from the = operator) that returns true when the two compared values are equal or both are NULL.

Conditional comparisons involving NULL are special. You cannot use = NULL or != NULL to find NULL values in a column.

In MySQL, any comparison of NULL with any other value (even NULL) always returns NULL. That is, NULL = NULL returns NULL.

Use the IS NULL and IS NOT NULL operators to handle NULL in MySQL.

Note:

SELECT *, columnName1 + IFNULL(columnName2, 0) FROM tableName;

If columnName1 and columnName2 are of type INT, and columnName2 contains a NULL value, then columnName1 + columnName2 would be NULL. IFNULL(columnName2, 0) converts the NULL value in columnName2 to 0.

Common Considerations and Tips for Handling NULL Values in MySQL

Checking for NULL:

To check if a column is NULL, you can use the IS NULL or IS NOT NULL condition.

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

Using the COALESCE Function to Handle NULL:
The COALESCE function can be used to replace NULL values. It accepts multiple parameters and returns the first non-NULL value from the parameter list:
```
SELECT product_name, COALESCE(stock_quantity, 0) AS actual_quantity
FROM products;
```
In the SQL statement above, if the stock_quantity column is NULL, COALESCE will return 0.
Using the IFNULL Function to Handle NULL:
The IFNULL function is a MySQL-specific version of COALESCE. It accepts two parameters and returns the second parameter if the first parameter is NULL.
```
SELECT product_name, IFNULL(stock_quantity, 0) AS actual_quantity
FROM products;
```
NULL Sorting:
When using the ORDER BY clause, NULL values are sorted last by default. If you want NULL values to appear first, you can 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;
```
Using the <=> Operator for NULL Comparison:
The <=> operator is a special operator in MySQL for comparing two expressions for equality, and it also returns TRUE for NULL value comparisons. It can be used for equality comparisons involving NULL values.
```
SELECT * FROM employees WHERE commission <=> NULL;
```
Note Aggregate Functions' Handling of NULL:
When using aggregate functions (like 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, also consider the use cases and reasonableness of NULL values.

Using NULL Values at the Command Prompt

The following examples assume the table tutorial_test_tbl in the database has two columns: tutorial_author and tutorial_count. The tutorial_count column is set to allow NULL values.

Example

Try the following examples:

Create the data table tutorial_test_tbl

root@host# mysql -u root -p password;
Enter password:*******
mysql> use ;
Database changed
mysql> create table tutorial_test_tbl
    -> (
    -> tutorial_author varchar(40) NOT NULL,
    -> tutorial_count  INT
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO tutorial_test_tbl (tutorial_author, tutorial_count) values ('', 20);
mysql> INSERT INTO tutorial_test_tbl (tutorial_author, tutorial_count) values ('', NULL);
mysql> INSERT INTO tutorial_test_tbl (tutorial_author, tutorial_count) values ('Google', NULL);
mysql> INSERT INTO tutorial_test_tbl (tutorial_author, tutorial_count) values ('FK', 20);
 
mysql> SELECT * from tutorial_test_tbl;
+---------------+--------------+
| tutorial_author | tutorial_count |
+---------------+--------------+
|         | 20           |
|   | NULL         |
| Google        | NULL         |
| FK            | 20           |
+---------------+--------------+
4 rows in set (0.01 sec)

In the following examples, you can see that the = and != operators do not work:

mysql> SELECT * FROM tutorial_test_tbl WHERE tutorial_count = NULL;
Empty set (0.00 sec)
mysql> SELECT * FROM tutorial_test_tbl WHERE tutorial_count != NULL;
Empty set (0.01 sec)

To find whether the tutorial_count column in the tutorial_test_tbl table is NULL, you must use IS NULL and IS NOT NULL, as shown in the following example:

mysql> SELECT * FROM tutorial_test_tbl WHERE tutorial_count IS NULL;
+---------------+--------------+
| tutorial_author | tutorial_count |
+---------------+--------------+
|   | NULL         |
| Google        | NULL         |
+---------------+--------------+
2 rows in set (0.01 sec)
 
mysql> SELECT * from tutorial_test_tbl WHERE tutorial_count IS NOT NULL;
+---------------+--------------+
| tutorial_author | tutorial_count |
+---------------+--------------+
|         | 20           |
| FK            | 20           |
+---------------+--------------+
2 rows in set (0.01 sec)

Handling NULL Values with PHP Scripts

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

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

MySQL ORDER BY Test:

<?php
$dbhost = 'localhost';  // MySQL server host address
$dbuser = 'root';            // MySQL username
$dbpass = '123456';          // MySQL user password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('Connection failed: ' . mysqli_error($conn));
}
// Set encoding to prevent Chinese garbled characters
mysqli_query($conn , "set names utf8");
 
if( isset($tutorial_count ))
{
   $sql = "SELECT tutorial_author, tutorial_count
           FROM  tutorial_test_tbl
           WHERE tutorial_count = $tutorial_count";
}
else
{
   $sql = "SELECT tutorial_author, tutorial_count
           FROM  tutorial_test_tbl
           WHERE tutorial_count IS NULL";
}
mysqli_select_db( $conn, '' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('Cannot read data: ' . mysqli_error($conn));
}
echo '<h2> IS NULL Test<h2>';
echo '<table border="1"><tr><td>Author</td><td>Login Count</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQL_ASSOC))
{
    echo "<tr>".
         "<td>{$row['tutorial_author']} </td> ".
         "<td>{$row['tutorial_count']} </td> ".
         "</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

The output result is shown in the following image:

MySQL Connection Usage

MySQL Regular Expressions

ByteDance Coding Plan

Supports mainstream large models like Doubao, GLM, DeepSeek, Kimi, MiniMax. Official supply, stable and reliable.

Configuration Guide

¥9.9 / Month

Subscribe Now

iFlytek Star Coding Plan

Includes free model call quotas. DeepSeek, GLM, Kimi, MiniMax. One-stop experience and deployment platform.

Configuration Guide

¥3.9 / Month

Subscribe Now

Click me to share notes

Deep Exploration

Web Services
Web Service
Programming Languages
Scripts
Software
Scripting Languages
Development Tools
Computer Science

Online Examples

HTML Examples
CSS Examples
JavaScript Examples
Ajax Examples
jQuery Examples
XML Examples
Java Examples

Character Sets & Tools

HTML Character Set Settings
HTML ASCII Character Set
JS Obfuscation/Encryption
PNG/JPEG Image Compression
HTML Color Picker
JSON Formatting Tool
Random Number Generator

Latest Updates

AI Ethics and Safety
How AI Works
AI Tool Landscape
Prompt Engineering
Introduction to AI
AI Beginner's Tutorial
Large Model Multimodality (M...

Site Information

Feedback
Disclaimer
About Us
Article Archive

Follow WeChat

YouTip

Mysql Null