NULL Value | Database

Introduction

In database, NULL is used to indicate the absence of any data value. For example, at the time of recording the customer information, the email may be unknown, so it is recorded as NULL in the database. NULL is used to signify missing or unknown values.

Comparison With NULL

NULL isn’t a value in the normal sense. For instance no two NULL are equal to each other i.e. NULL = NULL is FALSE. So IS NULL and IS NOT NULL are used to test for NULL values. For example

/* Create Table nullTest */
CREATE TABLE nullTest(
  id INT,
  firstName VARCHAR(10)
);

/* Insert two row */
INSERT INTO nullTest(id) VALUES(1);
INSERT INTO nullTest(id, firstName) VALUES(1, 'Mike');

/* Empty result set */
SELECT * FROM nullTest WHERE firstName=NULL

SELECT * FROM nullTest WHERE firstName IS NULL
/* Query result */
id   firstName
1    NULL

Three Valued logic

Result of a logical expression is TRUE or FALSE. However, when NULL is involved in the logical evaluation, the result is UNKNOWN. This is called a three-valued logic: TRUE, FALSE, and UNKNOWN. The results of the following comparisons are UNKNOWN:

NULL = 0
NULL <> 0
NULL > 0
NULL = NULL

Reference

Null (SQL)