NULL Value in SQL

What Is A NULL Value?

When we are inserting the data in the tables, it is possible that data for some fields are not available. Instead of adding garbage values to these fields, NULL is inserted in the fields.

Hence NULL represents a column field in the table with no value. NULL is not a datatype like "int", "char". Also, arithmetic operations on NULL values will result in NULL. Take this example, if we do 81 - NULL. It will result in NULL.

Let's create a customer table first and insert some sample data into the table.

While inserting the data in the table if we have to insert NULL in a column, we just enter NULL in the corresponding field.

The above query will insert the sample data into the table.

Test for NULL Values

Suppose the firm is sending promotional SMS to customers and require the details of the customer where C_CONTACTNO is not equal to NULL.

The above query will return an empty table. This is because comparison operators such as =, != don't work on NULL value as NULL is a state, and we can't compare a value with a NULL. Hence we have to use certain operators to test whether a NULL is present in the table or not.

IS NULL

IS NULL operator is used to test whether a NULL is present in the specified column.

Syntax:

SELECT * FROM tableName WHERE columnName IS NULL;

The above query will check the C_CONTACTNO column and if there is a field with NULL in the column, it will display the row containing that field.

IS NOT NULL

IS NOT NULL operator is used to test for non-null values in the specified column, like for the above case where the firm will be sending the SMS.

Syntax :

SELECT * FROM tableName WHERE columnName IS NOT NULL;

The above query will check the C_CONTACTNO column and if NULL is not present in a field of the column, it will display the row containing that field.

SET NULL Value in UPDATE Statement

Suppose Vaibhav reaches out to the firm and tells them that he lost the SIM Card of his contact number. So please mark the Contact Number of Vaibhav as NULL.
In the above scenario, we can use the assignment operator = to set Vaibhav's contact number as NULL.

The above query will set Vaibhav's contact number as NULL.

NULL Value Related Functions in MySQL

MySQL provides us with some functions, which are intended specifically for use with NULL values. These functions are:

• ISNULL()
• IFNULL()

ISNULL()

ISNULL() takes a single argument, it can be data like ISNULL("MySQL"), or it can be a NULL value ISNULL(NULL), or we can pass a column like ISNULL(columnName), and if this argument is NULL, it returns 1 (true), else it returns 0 (false). This function is used to check whether a field in the specified column is NULL or not.

Syntax:

ISNULL(expression)

Let's understand this with an example:
Suppose the firm wants to inform the customers whose email is not present in the table. So the firm wants the C_ID and C_NAME of the customers where C_EMAIL is NULL.

The above query will return the following output:

In the query, if any field of the column C_EMAIl is NULL, it will display the C_ID and C_NAME of the corresponding field.

IFNULL()

IFNULL() is similar to the if-else statement, which works with NULL. It takes two arguments as inputs. If the first argument is NOT NULL, the function returns the first argument as an output; otherwise, if the first argument is NULL, it returns the second argument as an output.

Syntax:

IFNULL(expression,value)

Let's understand this with an example. Suppose the firm wants to have details of all the customers, and for customers having some columns as NULL in their records, it should be replaced by "Not Available" in the output. We can use the following query to do this task.

The above query will return the following output:

As you can see in the query if a field in the column C_EMAIL contains a NULL, it will display "Not Available" for that field. The same goes for the C_CONTACTNO column.