How to Handle Null Values in MYSQL

INTRODUCTION

 
In this tutorial, I am going to explain about 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 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
  1. DROP TABLE IF EXISTS GrowthMindSets;  
  2.   
  3. CREATE TABLE GrowthMindSets(  
  4.     GMS_ID INT AUTO_INCREMENT PRIMARY KEY,  
  5.     First_Name VARCHAR(50) NOT NULL,  
  6.     Last_Name VARCHAR(50) NOT NULL,  
  7.     DESCRIPTION VARCHAR(255) NOT NULL,  
  8.     EmailAddress VARCHAR(100),  
  9.     PhoneNo BIGINT  
  10. );
Now, insert some dummy data into it.
  1. INSERT INTO GrowthMindSets(First_Name, Last_Name, DESCRIPTION, EmailAddress, PhoneNo) VALUES('Vatsa''Admin''Expert in Business''test@gmail.com''9876543211'),  
  2.     ('Onkar''Admin''Expert in Development''test1@gmail.com''9876543221'),  
  3.     ('Rohit''Admin''Expert in Communication'NULL'9876543231'),  
  4.     ('Jasmine''Admin''Expert in DBA''test@gmail.com'NULL),  
  5.     ('Anamika''Admin''Expert in BDA'NULLNULL); 
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
  1. SELECT * FROM GrowthMindSets WHERE tutorial_count = NULL;  
  2. 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.
  1. SELECT * FROM GrowthMindSets  
  2. WHERE EmailAddress IS NULL
 
  1. SELECT * FROM GrowthMindSets  
  2. WHERE EmailAddress IS NOT NULL
 

MySQL SET NULL Values in UPDATE Statement

 
By using the assignment operator (“=”), you can set any value of a column to NULL by using the Update Statement.
 
Example
  1. UPDATE growthmindsets  
  2. SET EmailAddress = NULL  
  3. WHERE GMS_ID = 4 and GMS_ID = 5 
By using the following query, you can check the output.
  1. SELECT * FROM GROWTHMINDSETS  
  2. WHERE EmailAddress IS NULL AND PhoneNo IS NULL 
 

NULL VALUES RELATED FUNCTIONS IN MYSQL

 
1) In MYSQL there are some functions intended specifically for use with NULL values which include ISNULL () and IFNULL (). ISNULL () is true if its argument is NULL and false otherwise.
 
Example
  1. SELECT ISNULL(NULL), ISNULL(0), ISNULL(1); 
 
2) IFNULL() takes two arguments. If the first argument is not NULL, that argument is returned; otherwise, the function returns its second argument.
 
Example 1
  1. SELECT IFNULL(NULL,'a'), IFNULL(0,'b'); 
 
Example 2
 
In this example, I will show you a query that returns the empty string (‘’) if its value is NULL.
  1. SELECT First_Name, Last_Name, Description, IFNULL(EmailAddress, ''), IFNULL(PhoneNo, ''FROM GROWTHMINDSETS; 
 
3) Other functions handle NULL values in various ways, so you have to know how a given function behaves. In many cases, passing a NULL value to a function results in a NULL return value.
 
Example
 
Any NULL argument passed to CONCAT() causes it to return NULL:
  1. SELECT CONCAT('a','b'), CONCAT('a',NULL,'b'); 
 
4) But not all functions behave that way. CONCAT_WS () (concatenate with separator) simply ignores NULL arguments entirely.
 
Example
  1. SELECT CONCAT_WS('/','a','b'), CONCAT_WS('/','a',NULL,'b'); 
 
Resources 
 
Here are some useful related resources:

CONCLUSION

 
In this article, I have discussed the concept of handling NULL values in MySQL with various examples.
 
I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL. 
 
Thanks for reading this article!