A NULL in an SQL function means that an argument of the SQL function is NULL. Most scalar functions return NULL for a NULL argument, and aggregate functions ignore NULLs.
NULLs in scalar functions
When NULLs appear, you can determine the NULLs by using the return value of the NVL() function. The syntax of the NVL function is NVL(expr1,expr2). If expr1 is not NULL, the function returns expr1; otherwise, it returns expr2.
In the following example, the value of the expr1 parameter of the NVL(expr1,0) function is NULL. Check the return value. The return value of the expr1 parameter is NULL. Therefore, 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.
NULLs in aggregate functions
Aggregate functions such as AVG(), MAX(), SUM(), and COUNT() ignore NULL records.
The following sample code shows how to insert data into the tbl_a table and execute the 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 following sample code shows the return values of aggregate functions such as AVG(), MAX(), and SUM(). NULL records are 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