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.

Using ISNULL in SQL

In this article, we are going to learn about the ISNULL() function to replace a null with a given value in SQL. Real examples are included to overcome problems in SQL Server & MySQL.

ISNULL() Function

The ISNULL() function is given in the following format. 

Syntax

SELECT ISNULL(Column,  Value)

Table 1. Syntax for ISNULL Function

Explanation of parameters used in the function:

• Column is the column name that will be checked for NULL values. It is a required parameter. 
• Value is the default value to be assigned if the column value is NULL. 

The function will return the column value if it is not null. Otherwise, it will return the default value.

Example

Let’s have an example to identify the approach of the ISNULL() function. Consider the following tables for the example.

Employees Table: 

Id	SectionId	Bonus
1	1	1000
2	2	NULL
3	1	3000
4	NULL	NULL
5	3	NULL
6	NULL	6000
7	5	NULL
8	NULL	7000
9	5	0
10	3	0

Table 2. Sample Data for Employees

SectionsTable: 

Id	Name
1	Planning
2	Consulting
3	QA
4	IT
5	R & D

Table 3. Sample Data for Sections

By using the following query, we can create sample data for the given example.

SQL Data Script:

CREATE TABLE Employees   (      Id INT NOT NULL,SectionId INT NULL,Bonus DECIMAL(12, 2) NULL,      PRIMARY KEY (Id)   ); CREATE TABLE Sections   (      Id INT NOT NULL,Name VARCHAR (10) NOT NULL,      PRIMARY KEY (Id)   ); INSERT Employees (Id,SectionId,Bonus) VALUES(1,1,1000), (2,2,NULL), (3,1,3000), (4,NULL,NULL), (5,3,NULL), (6,NULL,6000), (7,5,NULL), (8,NULL,8000), (9,5,0), (10,3,0) INSERT Sections (Id,Name) VALUES(1,'Planning'), (2,'Consulting'), (3,'QA'), (4,'IT'), (5,'R & D');

Script 1. Sample Data

We can use the following SQL query to get the employee data with sections.

SQL Script:

SELECT Employees.Id,Sections.Name,Employees.Bonus FROM   Employees LEFT JOIN Sections ON Employees.SectionId = Sections.Id;

Script 2. Converting to Lowercase

The result is shown below. 

Id	Section	Bonus
1	Planning	1000.00
2	Consulting	NULL
3	Planning	3000.00
4	NULL	NULL
5	QA	NULL
6	NULL	6000.00
7	R & D	NULL
8	NULL	8000.00
9	R & D	0.00
10	QA	0.00

Table 3. Employee Data with Sections

The following query shows how to use the ISNULL() function to retrieve data without nulls.

SQL Script:

SELECT Employees.Id, ISNULL(Sections.Name, '') AS Section, ISNULL(Bonus, 0)  AS Bonus FROM   Employees LEFT JOIN Sections ON Employees.SectionId = Sections.Id;

Script 3. Converting nulls

Then we will get the below result.

Id	Section	Bonus
1	Planning	1000.00
2	Consulting	0.00
3	Planning	3000.00
4		0.00
5	QA	0.00
6		6000.00
7	R & D	0.00
8		8000.00
9	R & D	0.00
10	QA	0.00

Table 4. Corrected Employee Data

Note

Nulls in the database may affect calculations, queries & functions. You may overcome the problem in the following ways.

1. Using ISNULL() function to update nulls in the database if necessary.
2. Using ISNULL() function in queries to replace null values in the result.