How to Handle Null Values in MYSQL?

Introduction

In this tutorial, I am going to explain working with NULL in MySQL with examples. This article will cover the following topics. Let’s see.

1. Introduction
2. MySQL NULL Value Examples
3. MySQL SET NULL Value in UPDATE Statement
4. NULL values related Functions in MySQL
5. Conclusion

The concept of the null value is a common source of problems for beginners. Null is the same thing as an empty string. In MySQL, the server does nothing to disallow null as the value of a distributed expression, whether it is a column value or the value of a user-supplied expression. Null is unusual because it doesn't represent a specific value the way that numeric, string, or temporal values do. Even though it is permitted to use Null as the value of an expression that must otherwise yield an integer, it is important to keep in mind that null is not a number.

A NULL is defined as a term whose value is different from 0 and an empty string, i.e. unknown value or an indication that the data is missing.

MySQL NULL Value Examples

We suppose that there is a table whose name is “GrowthMindsets” in “csharpcorner” database with some dummy data in it. Let’s see.

Create a table

DROP TABLE IF EXISTS GrowthMindSets;  
  
CREATE TABLE GrowthMindSets(  
    GMS_ID INT AUTO_INCREMENT PRIMARY KEY,  
    First_Name VARCHAR(50) NOT NULL,  
    Last_Name VARCHAR(50) NOT NULL,  
    DESCRIPTION VARCHAR(255) NOT NULL,  
    EmailAddress VARCHAR(100),  
    PhoneNo BIGINT  
);

Now, insert some dummy data into it.

INSERT INTO GrowthMindSets(First_Name, Last_Name, DESCRIPTION, EmailAddress, PhoneNo) VALUES('Vatsa', 'Admin', 'Expert in Business', '[email protected]', '9876543211'),  
    ('Onkar', 'Admin', 'Expert in Development', '[email protected]', '9876543221'),  
    ('Rohit', 'Admin', 'Expert in Communication', NULL, '9876543231'),  
    ('Jasmine', 'Admin', 'Expert in DBA', '[email protected]', NULL),  
    ('Anamika', 'Admin', 'Expert in BDA', NULL, NULL); 

Now, you can check your table data by typing the following query.

1. SELECT * FROM GrowthMindSets;

   

Note.

Here, we can see that symbol “=” and “!=” do not work with “NULL” values as follows

SELECT * FROM GrowthMindSets WHERE tutorial_count = NULL;  
SELECT * FROM GrowthMindSets WHERE tutorial_count != NULL; 

To find the records where the “EmailAddress” column is or not equal to NULL, the queries should be written like this.

SELECT * FROM GrowthMindSets  
WHERE EmailAddress IS NULL; 

SELECT * FROM GrowthMindSets  
WHERE EmailAddress IS NOT NULL;