How to work with empty or null values in MySQL

In MySQL, an empty string and a NULL value have different meaning and usage. In this article, we discuss when to use NULL values over an empty string, and the appropriate operators for both. We will look at sample data and step-by-step examples to learn more about them.

What are NULL values in MySQL?

First, let’s establish that NULL is not the same as an empty value. NULL means “unknown data” or “non-existent value”. It is not the same as a “0” value, an empty string or a literal string “NULL”. In conclusion, we can say that NULL is not a value, it is the absence of value.

When should we use NULL values?

Sometimes, the data corresponding to some field of a record is unknown or does not exist. In that case, we say that the field can contain a NULL value.

For example, in a Shoe table, we can have NULL values on the “price” field because it is possible that some shoes have not established the price for sale. 

In contrast, we have fields that can never be NULL. In our Shoe table, the “size” field should never be NULL. To do this, when creating the table, we must specify this field does not support null values:

1. CREATE TABLE `mydatabase`.`shoes` (
2. `id` INT NOT NULL ,
3. `model` VARCHAR(20) NOT NULL ,
4. `size` FLOAT NOT NULL ,
5. `color` VARCHAR(20) NULL ,
6. `price` FLOAT NOT NULL ,
7. PRIMARY KEY (`id`)
8. ) ENGINE = InnoDB;

Conversely, we add the keyword “NULL” if the field allows NULL values, as in the case of the “price” field.In the script to create the “Shoes” table, right after the field definition, we add the keyword “NOT NULL” to specify that a field does not support NULL values. This applies to the “size” field that we mentioned earlier.

By default, the fields are set to allow NULL values.

Insert NULL values

If we enter the data of a shoe, for which we have not yet defined the price, we can place “NULL” to show that it is priceless. The following is the insert statement to show us how we can insert a record with NULL values:

1. INSERT INTO SHOES (ID, MODEL, SIZE, COLOR, PRICE)
2. VALUES (100, 'A1', 7, 'White', NULL);

Note: NULL is not a string of characters, and should not be placed in quotation marks.

Result:

id	model	size	color	price
100	A1	7	White	NULL

We can also place NULL in the field “COLOR” if we do not know the color of the shoe we are going to enter. After running the following query, the “Shoes” table now contains two records.

1. INSERT INTO SHOES (ID, MODEL, SIZE, COLOR, PRICE)
2. VALUES(101, 'B2', 6, NULL, 20);

id	model	size	color	price
100	A1	7	White	NULL
101	B2	6	NULL	20

Result:

Empty String and NULL Value – Is there a difference?

To do this comparison, first, we must enter some records with empty values to our table:

1. INSERT INTO SHOES (ID, MODEL, SIZE, COLOR, PRICE) VALUES (102, 'C3', 8, '', 25);
2. INSERT INTO SHOES (ID, MODEL, SIZE, COLOR, PRICE) VALUES (103, '', 8, 'Red', 15);
3. INSERT INTO SHOES (ID, MODEL, SIZE, COLOR, PRICE) VALUES (104, 'E5', 8, '', NULL);

Adding the two records from our previous example, we now have the following records in our table.

Result:

id	model	size	color	price
100	A1	7	White	NULL
101	B2	6	NULL	20
102	C3	8		25
103		8	Red	15
104	E5	8		NULL

Filtering on an empty string

If we want to find which shoes have the color set to an empty string, we execute the following statement:

1. SELECT * FROM `SHOES` WHERE COLOR = '';

Result:

id	model	size	color	price
102	C3	8		25
104	E5	8		NULL

Filtering on a NULL value

If we want to find which shoes have the color field to NULL, we execute the following statement:

1. SELECT * FROM `shoes` WHERE COLOR IS NULL;

Result:

id	model	size	color	price
101	B2	6	NULL	20

Difference between an empty string and a NULL value

With the previous examples, we saw that an empty string is handled differently from a NULL value.

We also see a difference in the operator we use to compare. We cannot use the “=” operator to filter columns with NULL values. Instead, we should use IS NULL or IS NOT NULL.