Post
Ask Question

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; 

nullvalues

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; 

handlenullvalue

SELECT * FROM GrowthMindSets  
WHERE EmailAddress IS NOT NULL; 

addressnullvalue

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

UPDATE growthmindsets  
SET EmailAddress = NULL  
WHERE GMS_ID = 4 and GMS_ID = 5 

By using the following query, you can check the output.

SELECT * FROM GROWTHMINDSETS  
WHERE EmailAddress IS NULL AND PhoneNo IS NULL 

handlenullvaueinsql

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

SELECT ISNULL(NULL), ISNULL(0), ISNULL(1); 

functions

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

SELECT IFNULL(NULL,'a'), IFNULL(0,'b'); 

firstargumentnullvalue

Example 2

In this example, I will show you a query that returns the empty string (‘’) if its value is NULL.

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:

SELECT CONCAT('a','b'), CONCAT('a',NULL,'b'); 

nullargumentpassed

4. But not all functions behave that way. CONCAT_WS () (concatenate with separator) simply ignores NULL arguments entirely.

Example

SELECT CONCAT_WS('/','a','b'), CONCAT_WS('/','a',NULL,'b'); 

Nullargumentsentirely

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!


Similar Articles