SQL - Null Functions

NULL functions are provided to perform operations on NULL values that are stored in our database tables.

A NULL value is pke a placeholder in the database when we have data that is missing or the required data is not available. It is a flexible value that is not part of any practical data type and can be put in the column of any data type, pke string, int, varchar etc.

Following are the various features of a NULL value −

The NULL value is different from a zero value or a field that contains a space. A record with a NULL value is one that has been left blank during record creation.

The NULL value assists us in removing ambiguity from data. Also, a NULL value is beneficial to maintain the uniform datatype across the column.

If a user entered their DOB in the column of their mobile number, ambiguity could arise when contact was required. To overcome this, we have to check the data before insertion and update any data that is not of the date datatype with a NULL function.

NULL functions in SQL

With the abipty to recognize NULL data, one can perform operations on them similar to the aggregate methods in SQL. They are −

ISNULL()

COALESCE()

NULLIF()

IFNULL()

Let us discuss these functions one by one elaborately.

ISNULL() Function

The SQL server ISNULL() function lets you return an alternative value when an expression is NULL. Or we can say that it is used to replace the NULL value.

This function can be used in cases where we need to update the table. If one finally finds an appropriate value to fill in the blank space of a record, the NULL value will be replaced.

Syntax

Following is the syntax for the ISNULL() function −

SELECT ISNULL(column_name, value_to_return) FROM table_name;

Example

In the example below, we are replacing the NULL value of the salary in the customer s table with "500000."

Assume we have created a table named Customers, which contains the personal details of customers including their name, age, address and salary etc., using the following query

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1,  Ramesh , 32,  Ahmedabad , 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2,  Khilan , 25,  Delhi , 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3,  kaushik , 23,  Kota , NULL );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4,  Chaitap , 25,  Mumbai , 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5,  Hardik , 27,  Bhopal , 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6,  Komal , 22,  MP , NULL );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7,  Muffy , 24,  Indore , 10000.00 );

The table will be created as −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |   NULL   |
|  4 | Chaitap |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |   NULL   |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Following is the query to replace the null value −

SELECT ISNULL(SALARY, 500000) AS Result FROM CUSTOMERS;

Output

On execution of the above query, we get the column "salary", and the null value will be replaced with "500000", as we can see in the result column that follow −

+---------+
| Result  |
+---------+
|  2000   |
|  1500   |
|  50000  |
|  6500   |
|  8500   |
|  50000  |
|  10000  |
+---------+

COALESCE() Function

The COALESCE() function of the SQL server returns the first occurred not-NULL expression among its arguments. If all the expressions are NULL, then the COALESCE() function will return NULL.

An integer is always evaluated first in the COALESCE() function, and an integer followed by a character expression produces an integer as an output.

Syntax

Following is the syntax for the COALESCE() function −

SELECT COALESCE (expression_1, expression_2, …, expression_n) FROM table_name;

Example

In the following example, we are using the following query to return the first occurred not-null value.

SELECT COALESCE (NULL,  tutorialspoint , NULL) AS Result;

Output

On executing the above query, we get the "tutorialspoint" as result, because it is the first string which is followed by the NULL value, as we can see in the result column that follows −

+-----------------+
|   Result        |
+-----------------+
|  tutorialspoint |
+-----------------+

Example

In the following example, we use the COALESCE() function to retrieve the "name" and "salary", passing the arguments "age" and "salary" inside of coalesce() and making as a result. As we can see in the following query −

SELECT NAME, SALARY, COALESCE(SALARY, AGE) Result FROM  CUSTOMERS;

Output

On executing the above query, we get the name and salary, and using the COALESCE() function, we compare the salary and age. It will return as a result the value that is not null, as we can see in the output table.

+----------+------------+------------+
| NAME     | SALARY     | Result     |
+----------+------------+------------+
| Ramesh   |  2000.0000 |  2000.0000 |
| Khilan   |  1500.0000 |  1500.0000 |
| kaushik  |       NULL |    23.0000 |
| Chaitap |  6500.0000 |  6500.0000 |
| Hardik   |  8500.0000 |  8500.0000 |
| Komal    |       NULL |    22.0000 |
| Muffy    | 10000.0000 | 10000.0000 |
+----------+------------+------------+

NULLIF() Function

The NULLIF() function accepts two arguments. If both expressions are identical, it returns NULL. Otherwise, the first argument is returned. We can use this function with clauses pke SELECT, WHERE, and GROUP BY directly.

Syntax

Following is the syntax of NULLIF() Function −

SELECT column(s), NULLIF(expression_1, expression_2) FROM table_name;

Example

In the following example, we create a query with the WHERE clause that will return “name” and “age” from the Customers table with the help of the NULLIF() function. We are passing the age and salary as arguments to the NULLIF() function.

SELECT NAME, NULLIF(AGE, SALARY) AS Result FROM  CUSTOMERS WHERE SALARY IS NULL;

Output

On executing the above query, we are retrieving the name and age using the NULLIF() function. We are returning age as a result because the NULLIF() function returns the first argument, if both arguments are not identical. As we can see in the output table that follows −

+----------+---------+
| NAME     | Result  |
+----------+---------+
| Kaushik  |    23   |
| Komal    |    22   |
+----------+---------+

IFNULL() Function

The IFNULL() function is available only in MySQL and not in the SQL server. It accepts two arguments; if the first argument is not null, it returns the first argument; otherwise, it returns the second argument. It is commonly used to replace a NULL value with another value. And it returns either string or numeric value depending on the context where it is used.

Syntax

Following is the syntax for IFNULL() function −

SELECT column(s), IFNULL(column_name, value_to_replace) FROM table_name;

Example

In the following example, we are fetching ID, NAME, AGE, and SALARY and replacing the null salary with "550000" and an apas with Result. As we can see in the query that follows −

SELECT ID, NAME, AGE, SALARY, IFNULL(SALARY, 550000) AS Result FROM customers WHERE SALARY IS NULL;

Output

On executing the above query, we get all the details that we are fetching. As we can see in the output tables that follow −

+----+---------+-----+--------+-------------+
| ID | NAME    | AGE | SALARY | Result      |
+----+---------+-----+--------+-------------+
|  3 | kaushik |  23 |   NULL | 550000.0000 |
|  6 | Komal   |  22 |   NULL | 550000.0000 |
+----+---------+-----+--------+-------------+
2 rows in set (0.05 sec)