DBA Hub

📋Steps in this guide1/9

NULL-Related Functions

A summary of the functions available for handling NULL values.

oracle miscconfigurationintermediate
by OracleDba
13 views
1

Background

Most of the examples in this article require the following table. If we query the data in the table we see the following output. Remember, comparisons against null always result in null, so queries can't use regular comparison operators like "=" or "!=". Instead they must use the or operators.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
DROP TABLE null_test_tab;

CREATE TABLE null_test_tab (
  id   NUMBER,
  col1 VARCHAR2(10),
  col2 VARCHAR2(10),
  col3 VARCHAR2(10),
  col4 VARCHAR2(10)
);

INSERT INTO null_test_tab values (1, 'ONE', 'TWO', 'THREE', 'FOUR');
INSERT INTO null_test_tab values (2, NULL, 'TWO', 'THREE', 'FOUR');
INSERT INTO null_test_tab values (3, NULL, NULL, 'THREE', 'FOUR');
INSERT INTO null_test_tab values (4, NULL, NULL, 'THREE', 'THREE');
COMMIT;

SQL> SELECT * FROM null_test_tab ORDER BY id;

        ID COL1       COL2       COL3       COL4
---------- ---------- ---------- ---------- ----------
         1 ONE        TWO        THREE      FOUR
         2            TWO        THREE      FOUR
         3                       THREE      FOUR
         4                       THREE      THREE

4 rows selected.

SQL>

SQL> SELECT * FROM null_test_tab WHERE col1 = NULL ORDER BY id;

no rows selected

SQL>

SQL> SELECT * FROM null_test_tab WHERE col1 IS NULL ORDER BY id;

        ID COL1       COL2       COL3       COL4
---------- ---------- ---------- ---------- ----------
         2            TWO        THREE      FOUR
         3                       THREE      FOUR
         4                       THREE      THREE

3 rows selected.

SQL>
2

NVL

The function allows you to replace null values with a default value. If the value in the first parameter is null, the function returns the value in the second parameter. If the first parameter is any value other than null, it is returned unchanged. We know that COL1 in the test table contains null in all rows except the first. Using the function we replace the null values with 'ZERO'.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
SQL> SELECT id, NVL(col1, 'ZERO') AS output FROM null_test_tab ORDER BY id;

	ID OUTPUT
---------- ----------
      	 1 ONE
      	 2 ZERO
      	 3 ZERO
      	 4 ZERO

4 rows selected.

SQL>
3

DECODE

The function is not specifically for handling null values, but it can be used in a similar way to the function, as shown by the following example.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
SQL> SELECT id, DECODE(col1, NULL, 'ZERO', col1) AS output FROM null_test_tab ORDER BY id;

	ID OUTPUT
---------- ----------
	 1 ONE
	 2 ZERO
	 3 ZERO
	 4 ZERO

4 rows selected.

SQL>
4

NVL2

The function accepts three parameters. If the first parameter value is not null it returns the value in the second parameter. If the first parameter value is null, it returns the third parameter. The following query shows in action. The first row in the test table has a not null value in COL1, so the value of COL2 is returned. All other rows contains null in COL1, so the value of COL3 is returned.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
SQL> SELECT id, NVL2(col1, col2, col3) AS output FROM null_test_tab ORDER BY id;

	ID OUTPUT
---------- ----------
      	 1 TWO
      	 2 THREE
      	 3 THREE
      	 4 THREE

4 rows selected.

SQL>
5

COALESCE

The function was introduced in Oracle 9i. It accepts two or more parameters and returns the first non-null value in a list. If all parameters contain null values, it returns null.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
SQL> SELECT id, COALESCE(col1, col2, col3) AS output FROM null_test_tab ORDER BY id;

        ID OUTPUT
---------- ----------
         1 ONE
         2 TWO
         3 THREE
         4 THREE

4 rows selected.

SQL>
6

NULLIF

The function was introduced in Oracle 9i. It accepts two parameters and returns null if both parameters are equal. If they are not equal, the first parameter value is returned. In our test table the values of COL3 and COL4 are equal in row 4, so we would only expect null returned for that row using the following query.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
SQL> SELECT id, NULLIF(col3, col4) AS output FROM null_test_tab ORDER BY id;

        ID OUTPUT
---------- ----------
         1 THREE
         2 THREE
         3 THREE
         4

4 rows selected.

SQL>
7

LNNVL

The function has been available since at least Oracle 9i, but was undocumented until Oracle 11g. It is used in a where clause to evaluate a condition. If this condition evaluates to false or unknown, it returns true. If the condition evaluates to true, it returns false.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SQL> SELECT id, col3 FROM null_test_tab WHERE LNNVL(col1 IS NULL) ORDER BY id;

        ID COL3
---------- ----------
         1 THREE

1 row selected.

SQL> SELECT id, col3 FROM null_test_tab WHERE LNNVL(col2 = 'TWO') ORDER BY id;

        ID COL3
---------- ----------
         3 THREE
         4 THREE

2 rows selected.

SQL> SELECT id, col3 FROM null_test_tab WHERE LNNVL(col2 != 'TWO') ORDER BY id;

        ID COL3
---------- ----------
         1 THREE
         2 THREE
         3 THREE
         4 THREE

4 rows selected.

SQL>
8

NANVL

The function was introduced in Oracle 10g for use with the and datatypes, which can contain a special "Not a Number" or "NaN" value. The function is similar to , but rather than testing for null it tests for "NaN" values. The following table will be used to demonstrate it. If we query the table we see the following data. Next, we query the data again, but convert any "NaN" values to "0" using the function.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
DROP TABLE nanvl_test_tab;

CREATE TABLE nanvl_test_tab (
  id NUMBER,
  col1 BINARY_DOUBLE
);

INSERT INTO nanvl_test_tab VALUES (1, 1234.5678);
INSERT INTO nanvl_test_tab VALUES (2, 'INF');
INSERT INTO nanvl_test_tab VALUES (3, '-INF');
INSERT INTO nanvl_test_tab VALUES (4, 'NaN');
COMMIT;

SELECT * FROM nanvl_test_tab ORDER BY id;

        ID       COL1
---------- ----------
         1 1.235E+003
         2        Inf
         3       -Inf
         4        Nan

4 rows selected.

SQL>

SELECT id, col1, NANVL(col1, 0) AS output FROM nanvl_test_tab;

        ID       COL1     OUTPUT
---------- ---------- ----------
         1 1.235E+003 1.235E+003
         2        Inf        Inf
         3       -Inf       -Inf
         4        Nan          0

4 rows selected.

SQL>
9

SYS_OP_MAP_NONNULL

We have seen that a comparison of "NULL = NULL" will always return false, but sometimes you want it to return true. It is possible to make this happen using the and functions, but depending on how you use them this relies on you converting the null value to another value that you hope will never be present in the column or variable. An alternative is to use the undocumented function to allow null matches. Remember, this is an undocumented function, so strictly speaking it shouldn't be used in a production application. For more information see: - NULL-Related Functions - NVL - DECODE - NVL2 - COALESCE - NULLIF - LNNVL - NANVL Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
SELECT id, 'col1=col2'
FROM   null_test_tab
WHERE  NVL(col1, '!null!') = NVL(col2, '!null!');

        ID 'COL1=COL
---------- ---------
         3 col1=col2
         4 col1=col2

2 rows selected.

SQL>

SELECT id, 'col1=col2'
FROM   null_test_tab
WHERE  DECODE(col1, NULL, '!null!', col1) = DECODE(col2, NULL, '!null!', col2);

        ID 'COL1=COL
---------- ---------
         3 col1=col2
         4 col1=col2

2 rows selected.

SQL>

SELECT id, 'col1=col2'
FROM   null_test_tab
WHERE  DECODE(col1, col2, '!match!', col1) = '!match!';

        ID 'COL1=COL
---------- ---------
         3 col1=col2
         4 col1=col2

2 rows selected.

SQL>

SELECT id, 'col1=col2'
FROM   null_test_tab
WHERE  SYS_OP_MAP_NONNULL(col1) = SYS_OP_MAP_NONNULL(col2);

        ID 'COL1=COL
---------- ---------
         3 col1=col2
         4 col1=col2

2 rows selected.

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!