All Products
Search
Document Center

Null values in SQL functions

Last Updated: Jun 18, 2021

Null values in SQL functions refer to the null arguments in the functions. If you pass null arguments to SQL functions, most scalar functions return NULL, and analytic functions ignore the null arguments. You can determine whether a null value occurs based on the return value of the NVL function.

Null value in the NVL function

NVL(expr1,expr2) is the expression of the NVL function. If expr1 is not NULL, expr1 is returned. Otherwise, expr2. is returned.

In the following example, the statement is used to query the return value of the NVL(expr1,0) expression when the expr1 parameter is set to NULL.

Execute the following statement:

SELECT NVL(NULL,0) FROM DUAL;

The following result is returned:

+-------------+
| NVL(NULL,0) |
+-------------+
|           0 |
+-------------+

If expr1 is NULL, the NVL(expr1,0) expression returns 0. If expr1 is not NULL, the expression returns NULL.

Null values in analytic functions

When you use analytic functions such as AVG, MAX, SUM, and COUNT, NULL records are ignored.

In the following example, the data is inserted into the tbl_a table, and the following statement is executed:

CREATE TABLE tbl_a (col_a varchar2(1),  col_b int );
INSERT INTO tbl_a VALUES (NULL, 3);
INSERT INTO tbl_a VALUES (NULL, NULL);
INSERT INTO tbl_a VALUES (NULL, 1);

Execute the following statement:

SELECT * FROM tb1_a;

The following result is returned:

+-------+-------+
| COL_A | COL_B |
+-------+-------+
|  NULL |   3   |
+-------+-------+
|  NULL |  NULL |
+-------+-------+
|  NULL |   1   |
+-------+-------+

The following result is returned:

SELECT AVG(col_b) FROM tbl_a; -- The result is 2.
SELECT MAX(col_b) FROM tbl_a; -- The result is 3.
SELECT SUM(col_b) FROM tbl_a; -- The result is 4.
SELECT COUNT(col_b) FROM tbl_a; -- The result is 2. 
SELECT COUNT(col_a) FROM tbl_a; -- The result is 0.
SELECT COUNT(*) FROM tbl_a; -- The result is 3.

NULL records are ignored.