Understanding NULL Values in MySQL

The article starts with a small experiment to illustrate how MySQL handles NULL values by creating a simple table t_student and inserting several rows, some of which contain NULL in the stu_no column.

CREATE TABLE `t_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` varchar(10) DEFAULT NULL COMMENT '学生编号',
  `stu_name` varchar(50) DEFAULT NULL COMMENT '学生姓名',
  `class_id` int(11) DEFAULT NULL COMMENT '班级id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

After inserting test data, the author runs queries using = NULL and != NULL , both of which return no rows, highlighting that direct equality or inequality comparisons with NULL do not work in MySQL.

SELECT * FROM t_student WHERE stu_no = NULL;

SELECT * FROM t_student WHERE stu_no != NULL;

The article then cites MySQL’s official documentation, explaining that NULL represents an unknown value and any comparison with NULL yields NULL, not true or false. Therefore, NULL is not the same as an empty string.

To correctly retrieve rows containing NULL, the article demonstrates using the IS NULL and IS NOT NULL predicates.

SELECT * FROM t_student WHERE stu_no IS NULL;

It also introduces the IFNULL(expr1, expr2) function, which returns expr2 when expr1 is NULL.

Next, the article explains that in contexts such as DISTINCT , GROUP BY , or ORDER BY , MySQL treats all NULL values as equal. An example using SELECT DISTINCT stu_no FROM t_student shows that the two NULL entries are considered the same, resulting in three distinct values.

SELECT DISTINCT stu_no FROM t_student;

Finally, the behavior of aggregate functions with NULL is covered: functions like SUM() , MIN() , and COUNT(column) ignore NULL values, while COUNT(*) counts rows regardless of NULLs.

SELECT COUNT(*), COUNT(stu_no) FROM t_student;

The article concludes with three key points: (1) NULL is unknown and must be tested with IS (NOT) NULL or IFNULL ; (2) NULL is considered equal in grouping, sorting, and deduplication operations; (3) Aggregate functions skip NULL except for COUNT(*) .