How to Handle NULL in Oracle SQL? & What is NULL Handling?

NULL in Oracle

In Oracle, NULL values are different from zero or an empty string because they indicate missing, unknown, or irrelevant data. For precise data retrieval and manipulation, it is essential to comprehend how NULLs behave in queries and functions.

An outline of the behaviour of NULL values is provided below:

The Nature of NULL Values

No matter what kind of data it contains, a column is deemed NULL if it contains no values. Remembering that a NULL is not the same as zero or a blank space is crucial. An unknown age for a person in an age-tracking table, for instance, is NULL rather than zero since zero would suggest a known age of zero. Similar to this, a flat number for a house may be NULL since it isn’t important, not because it isn’t known but rather because it is.

In SQL and PL/SQL, Oracle Database also handles a zero-length character string (”) as NULL. Columns in VARCHAR2 tables behave in this way. However, unlike in the database, where putting a zero-length text into a CHAR(n) column results in NULL, in PL/SQL, a zero-length string allocated to a CHAR(n) variable is blank-padded and does not become NULL.

NULLs in Expressions and Calculations

In all cases, any arithmetic expression that contains a NULL value will evaluate to NULL. This is due to the fact that a calculation’s outcome is also unknown if one of its components is unknown.

Example:

SELECT 3 * NULL + 5, 'Hello ' || NULL || 'world' FROM DUAL;

Output:

3*NULL+5 'HELLO'||NULL||'WORLD'
---------- ---------------------
(null)     Hello world

As can be seen, string concatenation || is an exception and will essentially disregard the NULL, merging the non-NULL portions, whereas 3 * NULL + 5 yields NULL. The outcome is NULL if the two strings being concatenated are both NULL.

NULLs in Comparison Conditions

With NULL values, you cannot use common comparison operators such as =, !=, <, >, etc.. Using these operators to compare any value to NULL or to compare two NULLs will yield UNKNOWN. Only when two NULLs appear in compound keys or are evaluated by a DECODE function does Oracle treat them as equal.

You must use the IS NULL or IS NOT NULL operators to check for the existence or absence of NULLs.

Example: To find rows where Precipitation is unknown (NULL):

select City, SampleDate, Precipitation from COMFORT where Precipitation IS NULL;

Output:

CITY          SAMPLEDAT PRECIPITATION
------------- --------- -------------
KEENE         23-SEP-03

To find rows where Precipitation is known (NOT NULL):

select City, SampleDate, Precipitation from COMFORT where Precipitation IS NOT NULL;

Output:

CITY          SAMPLEDAT PRECIPITATION
------------- --------- -------------
SAN FRANCISCO 21-MAR-03            .5
SAN FRANCISCO 22-JUN-03            .1
SAN FRANCISCO 23-SEP-03            .1
SAN FRANCISCO 22-DEC-03           2.3
KEENE         21-MAR-03           4.4
KEENE         22-JUN-03           1.3
KEENE         22-DEC-03           3.9

NULLs in and Clauses

No rows will be returned if a condition in a WHERE clause evaluates to UNKNOWN. When NOT IN circumstances are present, this might be more challenging. The entire condition evaluates to UNKNOWN or FALSE for every row in the list that results from a NOT IN operation, therefore no rows will be returned if the list contains any NULL values.

Example:

SELECT 'True' FROM employees WHERE department_id NOT IN (10, 20, NULL);

This statement would return no rows because the presence of NULL in the NOT IN list makes the condition UNKNOWN for all comparisons.

The LNNVL function provides a way to evaluate conditions that might involve NULLs, returning TRUE if the condition is FALSE or UNKNOWN, and FALSE if the condition is TRUE. It can be used in the WHERE clause to account for potential NULLs where IS [NOT] NULL, AND, or OR conditions might not be valid.

NULL-Related Functions

Specifically designed to handle NULL values, Oracle offers the following functions:

1. NVL(value, substitute) This function replaces a NULL value with a specified substitute. If value is not NULL, NVL returns value. Substitute can be a literal, another column, or a computation. NVL can be used with CHAR, VARCHAR2, DATE, and other data types, but value and substitute must be of compatible data types.
2. NVL2(expr1, expr2, expr3) This function returns expr2 if expr1 is not NULL, and expr3 if expr1 is NULL. expr1 can be of any data type, while expr2 and expr3 can be any data types except LONG.
3. COALESCE(value1, value2, …) COALESCE returns the first non-NULL expression in a list. If all expressions evaluate to NULL, the function returns NULL. Oracle uses short-circuit evaluation, meaning it stops evaluating expressions once it finds the first non-NULL value. This can be more efficient than nested NVL functions for multiple checks.
4. NULLIF(expr1, expr2) NULLIF compares expr1 and expr2. If they are equal, the function returns NULL; otherwise, it returns expr1. expr1 cannot be the literal NULL.
5. NANVL(value1, value2) For BINARY_FLOAT and BINARY_DOUBLE numbers, this function returns value2 if value1 is “not a number” (NaN); otherwise, it returns value1. This is useful for mapping NaN values to NULL.

NULLs in Group Functions

Aggregate functions, sometimes referred to as group functions, handle NULL values differently than single-value functions; the majority of aggregate functions disregard NULL values. They only use the group’s non-NULL values for calculations.

• AVG(value): Calculates the average of non-NULL values.
• SUM(value): Calculates the sum of non-NULL values.
• MAX(value) and MIN(value): Find the maximum/minimum among non-NULL values.
• STDDEV(value) and VARIANCE(value): Are measures of central tendency and relatively insensitive to missing data.

Example:

select AVG(Noon), COUNT(Noon), MAX(Noon), MIN(Noon), SUM(Noon)
from COMFORT
where City = 'SAN FRANCISCO';

Output:

AVG(NOON) COUNT(NOON) MAX(NOON) MIN(NOON) SUM(NOON)
--------- ----------- --------- --------- ---------
55.4           3      62.5      51.1     166.2

AVG(Noon) and SUM(Noon) in this report are based on just three known temperatures because one Noon value was NULL. Only the non-NULL values in the Noon column are counted by COUNT(Noon).

COUNT(*) is a special case that only counts rows with NULL values in any field and never returns NULL. COUNT(expr) only counts expr values that are not NULL.

Example:

select COUNT(DISTINCT City), COUNT(City), COUNT(*) from COMFORT;

Output:

COUNT(DISTINCTCITY) COUNT(CITY) COUNT(*)
------------------- ----------- --------
2                             8        8

Here, COUNT(City) counts all 8 rows, as City is NOT NULL, and COUNT(*) also returns 8.

Other Important Considerations for NULLs

GROUP BY and NULL values

The GROUPING function is used in conjunction with GROUP BY to differentiate between NULLs representing super-aggregate rows produced by ROLLUP or CUBE operations and NULLs that are genuine data values. It yields 0 otherwise and 1 for super-aggregate NULLs.

Updating to NULL

You can explicitly set a column’s value to NULL using an UPDATE statement, for instance: UPDATE COMFORT SET Noon = NULL WHERE City = 'KEENE' AND SampleDate = '22-DEC-03'. This is the sole instance where the equal sign (=) is used directly with NULL.

PL/SQL BOOLEAN and NULL

The BOOLEAN data type in PL/SQL can store TRUE, FALSE, or NULL. Conditional logic can be greatly impacted by NULL in Boolean expressions, which could have unforeseen consequences if left unchecked.

To avoid logical mistakes and guarantee the accuracy of results in Oracle databases, NULL values must be handled correctly.