Null values in SQL functions refer to the presence of null values in the parameters of SQL functions. When a parameter of an SQL function is null, most scalar functions return NULL, and analytic functions ignore null values.
Null values in scalar functions
When a null value is encountered, you can use the return value of the NVL() function to determine the null value. The expression of the NVL() function is NVL(expr1,expr2). If expr1 is not NULL, it returns expr1; otherwise, it returns expr2.
As shown in the following example, specify expr1 as NULL and query the return value of the NVL(expr1, 0) expression. If expr1 is NULL, the return value of the NVL(expr1, 0) expression is 0.
obclient> SELECT NVL(NULL,0) FROM DUAL;
+-------------+
| NVL(NULL,0) |
+-------------+
| 0 |
+-------------+
1 row in set
For more information about the NVL function, see NVL.
Null values in aggregate functions
When using aggregate functions such as AVG, MAX, SUM, and COUNT, records with NULL values are ignored.
As shown in the following example, insert data into the tbl_a table and execute a query statement.
obclient> CREATE TABLE tbl_a (col_a varchar2(1), col_b int );
Query OK, 0 rows affected (0.15 sec)
obclient> INSERT INTO tbl_a VALUES (NULL, 3);
Query OK, 1 row affected
obclient> INSERT INTO tbl_a VALUES (NULL, NULL);
Query OK, 1 row affected
obclient> INSERT INTO tbl_a VALUES (NULL, 1);
Query OK, 1 row affected
obclient> SELECT * FROM tbl_a;
+-------+-------+
| COL_A | COL_B |
+-------+-------+
| NULL | 3 |
| NULL | NULL |
| NULL | 1 |
+-------+-------+
3 rows in set
The return values of aggregate functions such as AVG, MAX, and SUM are as follows, with records containing NULL values being ignored.
obclient> SELECT AVG(col_b) FROM tbl_a;
+------------+
| AVG(COL_B) |
+------------+
| 2 |
+------------+
1 row in set
obclient> SELECT MAX(col_b) FROM tbl_a;
+------------+
| MAX(COL_B) |
+------------+
| 3 |
+------------+
1 row in set
obclient> SELECT SUM(col_b) FROM tbl_a;
+------------+
| SUM(COL_B) |
+------------+
| 4 |
+------------+
1 row in set
obclient> SELECT COUNT(col_b) FROM tbl_a;
+--------------+
| COUNT(COL_B) |
+--------------+
| 2 |
+--------------+
1 row in set
obclient> SELECT COUNT(col_a) FROM tbl_a;
+--------------+
| COUNT(COL_A) |
+--------------+
| 0 |
+--------------+
1 row in set
obclient> SELECT COUNT(*) FROM tbl_a;
+----------+
| COUNT(*) |
+----------+
| 3 |
+----------+
1 row in set