Oracle SQL NULL Handling Guide: IN, NOT IN, EXISTS & Aggregation Functions

Oracle SQL | Handling NULL Values in Oracle SQL

Understanding NULL Values
NULL Behavior with Aggregation Functions
NULL Handling in Conditional Operators
1. Operator: IN
2. Operator: NOT IN
NULL Behavior with Existence Operators
1. Operator: EXISTS
  1. True condition behavior
  2. False condition behavior
2. Operator: NOT EXISTS
  1. True condition behavior
NULL Handling in Set Operations
NULL Behavior with DISTINCT
NULL Handling in Grouping and Sorting
Quick Reference Summary

Understanding NULL Values
In Oracle SQL, NULL represents the absence of a value or an unknown value. The fundamental principle is that NULL is not equal to anything, including itself, and most operations involving NULL result in NULL.

Key principles of NULL handling:
- NULL comparisons: The NULL value cannot be compared to any other value using standard comparison operators (=, !=, <, >, etc.). The NULL value is neither equal, nor greater than, nor less than, nor different from any other value, even if the other value is also NULL.
  
  All comparison operations with NULL evaluate to UNKNOWN, which is treated as FALSE in WHERE clauses and other boolean contexts.
  
  Example:
```
SELECT  1 AS v1
FROM    DUAL
WHERE   NULL = NULL
OR      NULL != NULL
OR      NULL = 1
OR      NULL != 1
```
  Query result :
```
v1
--
```
- NULL arithmetic operations: The NULL value cannot be added, subtracted, multiplied, or divided by any other value, even if the other value is also NULL.
  
  Any arithmetic operation involving NULL propagates the NULL value, meaning the result is always NULL. This includes concatenation operations as well.
  
  Example:
```
SELECT  NULL + NULL AS v1
,       NULL + 1 AS v2
FROM    DUAL
```
  Query result :
```
v1     v2
------ ------
(null) (null)
```
- Testing for NULL values: To test if a value is NULL or not, you must use the predicates IS NULL and IS NOT NULL. These are the only reliable ways to check for NULL values in Oracle SQL.
  
  Example 1 :
```
SELECT  1 AS v1
FROM    DUAL
WHERE   NULL IS NULL
```
  Query result :
```
v1
--
 1
```
  Example 2 :
```
SELECT  1 AS v1
FROM    DUAL
WHERE   1 IS NOT NULL
```
  Query result :
```
v1
--
 1
```

NULL Behavior with Aggregation Functions

Oracle's aggregation functions have specific rules for handling NULL values.

General behavior: Most aggregation functions ignore NULL values when processing a column. However, if all values in the aggregated set are NULL or if no rows are returned, the behavior differs between COUNT and other functions.

Notes:

COUNT returns 0 when all values are NULL or no rows exist or when passing a literal NULL value as an argument.
Other functions (SUM, MIN, MAX, AVG) return NULL when all values are NULL or no rows exist or when passing a literal NULL value as an argument.

Examples:

Example 1: The column a.v1 is NULL for all rows in the query result.

SELECT  COUNT(a.v1), SUM(a.v1), MIN(a.v1), MAX(a.v1), AVG(a.v1)
FROM
(
    SELECT NULL AS v1
    FROM   DUAL

    UNION ALL

    SELECT  NULL AS v1
    FROM    DUAL
) a

Query result :

COUNT(a.v1) SUM(a.v1) MIN(a.v1) MAX(a.v1) AVG(a.v1)
----------- --------- --------- --------- ---------
          0 (null)    (null)    (null)    (null)

Example 2: The query returns no rows.

SELECT  COUNT(a.v1), SUM(a.v1), MIN(a.v1), MAX(a.v1), AVG(a.v1)
FROM
(
    SELECT  NULL AS v1
    FROM    DUAL
    WHERE   1=0
) a

Query result :

COUNT(a.v1) SUM(a.v1) MIN(a.v1) MAX(a.v1) AVG(a.v1)
----------- --------- --------- --------- ---------
          0 (null)    (null)    (null)    (null)

Example 3: Explicitly passing the NULL value as an argument to these functions !!

SELECT  COUNT(NULL), SUM(NULL), MIN(NULL), MAX(NULL), AVG(NULL)
FROM
(
    SELECT 1 AS v1
    FROM   DUAL
) a

Query result :

COUNT(a.v1) SUM(a.v1) MIN(a.v1) MAX(a.v1) AVG(a.v1)
----------- --------- --------- --------- ---------
          0 (null)    (null)    (null)    (null)

Example 4: The column a.v1 is NULL for some rows in the query result.
The functions ignore rows where the column a.v1 is NULL.

SELECT  COUNT(a.v1), SUM(a.v1), MIN(a.v1), MAX(a.v1), AVG(a.v1)
FROM
(
    SELECT  1 AS v1
    FROM    DUAL

    UNION ALL

    SELECT  2 AS v1
    FROM    DUAL

    UNION ALL

    SELECT  NULL AS v1
    FROM    DUAL
) a

Query result :

COUNT(a.v1) SUM(a.v1) MIN(a.v1) MAX(a.v1) AVG(a.v1)
----------- --------- --------- --------- ---------
          2         3         1         2       1.5

Example 5: Explicitly passing a numeric value as an argument to these functions !!
It is possible to specify a numeric value as an argument to these functions, in which case their behavior is to apply the operation of the function to the argument value for each row returned by the query.

The COUNT function is an exception to this rule, as it does not consider the value of the argument, as long as the value is not NULL, of course.

The COUNT function can also take the character "*" as an argument.

Example:
```
SELECT  COUNT(10), SUM(10), MIN(10), MAX(10), AVG(10)
FROM
(
    SELECT  NULL AS v1
    FROM    DUAL

    UNION ALL

    SELECT  1 AS v1
    FROM    DUAL
) a
```
Query result :
```
COUNT(a.v1) SUM(a.v1) MIN(a.v1) MAX(a.v1) AVG(a.v1)
----------- --------- --------- --------- ---------
          2        20        10        10        10
```

NULL Handling in Conditional Operators

The IN and NOT IN operators have complex behavior when dealing with NULL values.

Operator: IN

Syntax: expression IN (expression_list)

The IN operator performs multiple equality comparisons and returns TRUE if any comparison succeeds. Since NULL comparisons always return UNKNOWN (treated as FALSE), NULL values in either the expression or the list can affect the result.

"expression_list" can be a list of values or a subquery that can return multiple rows (with a single column).

The value of "expression" is compared with each element in the "expression_list".

Notes:

The condition is true if the value of "expression" is equal to at least one element in "expression_list".
The condition is false if the value of "expression" is different from each element in "expression_list".
The condition always returns false if the value of "expression" is NULL, the list of elements is empty, or the elements in the subquery are all NULL.

Standard matching behavior

Example 1:

SELECT  1 AS v1
FROM    DUAL
WHERE   1 IN (1,2,NULL)

Example 2:

SELECT  a.v1
FROM
(
    SELECT 1 AS v1
    FROM   DUAL
) a
WHERE   a.v1 IN
        (
            SELECT b.v2
            FROM
            (
                SELECT  1 AS v2
                FROM    DUAL

                UNION ALL

                SELECT  2 AS v2
                FROM    DUAL

                UNION ALL

                SELECT  NULL AS v2
                FROM    DUAL
            ) b
        )

Queries results:
```
v1
--
 1
```

Behavior with all NULL values or empty lists

Example 1:

SELECT  1 AS v1
FROM    DUAL
WHERE   1 IN (NULL,NULL)

Example 2:

SELECT  a.v1
FROM
(
    SELECT 1 AS v1
    FROM   DUAL
) a
WHERE   a.v1 IN
        (
            SELECT b.v2
            FROM
            (
                SELECT  NULL AS v2
                FROM    DUAL

                UNION ALL

                SELECT  NULL AS v2
                FROM    DUAL
            ) b
        )

Example 3:

SELECT  a.v1
FROM
(
    SELECT 1 AS v1
    FROM   DUAL
) a
WHERE   a.v1 IN
        (
            SELECT b.v2
            FROM
            (
                SELECT  1 AS v2
                FROM    DUAL
                WHERE   1=0
            ) b
        )

Queries results:
```
v1
--
```

Behavior when expression is NULL

Example 1:

SELECT  1 AS v1
FROM    DUAL
WHERE   NULL IN (1,2,NULL)

Example 2:

SELECT  a.v1
FROM
(
    SELECT NULL AS v1
    FROM   DUAL
) a
WHERE   a.v1 IN
        (
            SELECT b.v2
            FROM
            (
                SELECT  1 AS v2
                FROM    DUAL

                UNION ALL

                SELECT  2 AS v2
                FROM    DUAL

                UNION ALL

                SELECT  NULL AS v2
                FROM    DUAL
            ) b
        )

Queries results:
```
v1
--
```

Operator: NOT IN

Syntax: expression NOT IN (expression_list)

The NOT IN operator is particularly tricky with NULL values. It requires that the expression be different from ALL values in the list. If any comparison results in UNKNOWN (due to NULL), the entire condition becomes FALSE.

"expression_list" can be a list of values or a subquery that can return multiple rows (with a single column).

The value of "expression" is compared with each element in the "expression_list".

Notes:

The condition is true if the value of "expression" is different from each element in "expression_list".
The condition is true if the list of elements is empty.
The condition returns false if the value of "expression" is equal to at least one element in "expression_list".
The condition always returns false if the value of "expression" is NULL or at least one element in "expression_list" is NULL.

Behavior with NULL values in list

Example 1:

SELECT  5 AS v1
FROM    DUAL
WHERE   5 NOT IN (1,2,NULL)

Example 2:

SELECT  a.v1
FROM
(
    SELECT 5 AS v1
    FROM   DUAL
) a
WHERE   a.v1 NOT IN
        (
            SELECT b.v2
            FROM
            (
                SELECT  1 AS v2
                FROM    DUAL

                UNION ALL

                SELECT  2 AS v2
                FROM    DUAL

                UNION ALL

                SELECT  NULL AS v2
                FROM    DUAL
            ) b
        )

Queries results:
```
v1
--
```

Behavior when expression is NULL

Example 1 :

SELECT  NULL AS v1
FROM    DUAL
WHERE   NULL NOT IN (1,2,NULL)

Example 2 :

SELECT  a.v1
FROM
(
    SELECT NULL AS v1
    FROM   DUAL
) a
WHERE   a.v1 NOT IN
        (
            SELECT b.v2
            FROM
            (
                SELECT  1 AS v2
                FROM    DUAL

                UNION ALL

                SELECT  2 AS v2
                FROM    DUAL

                UNION ALL

                SELECT  NULL AS v2
                FROM    DUAL
            ) b
        )

Queries results:
```
v1
--
```

Behavior with empty lists

Example:

SELECT  a.v1
FROM
(
    SELECT 5 AS v1
    FROM   DUAL
) a
WHERE   a.v1 NOT IN
        (
            SELECT b.v2
            FROM
            (
                SELECT  1 AS v2
                FROM    DUAL
                WHERE   1=0
            ) b
        )

Query result :

v1
--
 5

NULL Behavior with Existence Operators

The EXISTS and NOT EXISTS operators behave differently from IN and NOT IN when dealing with NULL values. These operators only care about whether the subquery returns any rows, not the actual values returned.

Operator: EXISTS

Syntax: EXISTS (subquery)

The EXISTS operator returns TRUE if the subquery returns at least one row, regardless of whether those rows contain NULL values. The actual data values in the subquery result are irrelevant.

Note: The presence or absence of NULL values only affects the subquery: as long as the subquery returns a row, the EXISTS operator returns true; otherwise, it returns false.

True condition behavior

Example:

SELECT  a.v1
FROM
(
    SELECT 1 AS v1
    FROM   DUAL
) a
WHERE   EXISTS
        (
            SELECT b.v2
            FROM
            (
                SELECT  1 AS v2
                FROM    DUAL

                UNION ALL

                SELECT  2 AS v2
                FROM    DUAL

                UNION ALL

                SELECT  NULL AS v2
                FROM    DUAL
            ) b
            WHERE   b.v2 = a.v1
        )

Query result:

v1
--
 1

False condition behavior

Example 1:

SELECT  a.v1
FROM
(
    SELECT 1 AS v1
    FROM   DUAL
) a
WHERE   EXISTS
        (
            SELECT b.v2
            FROM
            (
                SELECT  1 AS v2
                FROM    DUAL
                WHERE   1=0
            ) b
            WHERE   b.v2 = a.v1
        )

Example 2:

SELECT  a.v1
FROM
(
    SELECT NULL AS v1
    FROM   DUAL
) a
WHERE   EXISTS
        (
            SELECT b.v2
            FROM
            (
                SELECT  1 AS v2
                FROM    DUAL

                UNION ALL

                SELECT  2 AS v2
                FROM    DUAL

                UNION ALL

                SELECT  NULL AS v2
                FROM    DUAL
            ) b
            WHERE   b.v2 = a.v1
        )

Queries results:
```
v1
--
```

Operator: NOT EXISTS

Syntax: NOT EXISTS (subquery)

The NOT EXISTS operator returns TRUE if the subquery returns no rows. Unlike NOT IN, the presence of NULL values in the subquery does not affect the result - only the row count matters.

Note: Be careful, the behavior of the NOT EXISTS operator is completely different from the NOT IN operator when it comes to NULL values; the presence or absence of NULL values only affects the subquery: as long as the subquery does not return any rows, the NOT EXISTS operator returns true; otherwise, it returns false.

True condition behavior

Example 1:

SELECT  a.v1
FROM
(
    SELECT 5 AS v1
    FROM   DUAL
) a
WHERE   NOT EXISTS
        (
            SELECT b.v2
            FROM
            (
                SELECT  1 AS v2
                FROM    DUAL

                UNION ALL

                SELECT  2 AS v2
                FROM    DUAL

                UNION ALL

                SELECT  NULL AS v2
                FROM    DUAL
            ) b
            WHERE   b.v2 = a.v1
        )

Query result:

v1
--
 5

Example 2:

SELECT  a.v1
FROM
(
    SELECT NULL AS v1
    FROM   DUAL
) a
WHERE   NOT EXISTS
        (
            SELECT b.v2
            FROM
            (
                SELECT  1 AS v2
                FROM    DUAL

                UNION ALL

                SELECT  2 AS v2
                FROM    DUAL

                UNION ALL

                SELECT  NULL AS v2
                FROM    DUAL
            ) b
            WHERE   b.v2 = a.v1
        )

Query result:

v1
------
(null)

Example 3:

SELECT  a.v1
FROM
(
    SELECT 5 AS v1
    FROM   DUAL
) a
WHERE   NOT EXISTS
        (
            SELECT b.v2
            FROM
            (
                SELECT  1 AS v2
                FROM    DUAL
                WHERE   1=0
            ) b
            WHERE   b.v2 = a.v1
        )

Query result:

v1
--
 5

NULL Handling in Set Operations
Set operations in Oracle SQL treat NULL values differently than comparison operators. For set operations, NULL values are considered equal to other NULL values and distinct from all non-NULL values. This behavior is consistent across all set operations. This allows set operations to properly eliminate or combine duplicate NULL values.
- Example 1: UNION
```
SELECT  NULL AS v1
FROM    DUAL

UNION

SELECT  NULL AS v1
FROM    DUAL
```
  Query result:
```
v1
------
(null)
```
- Example 2: UNION ALL
```
SELECT  NULL AS v1
FROM    DUAL

UNION ALL

SELECT  NULL AS v1
FROM    DUAL
```
  Query result:
```
v1
------
(null)
(null)
```
- Example 3: INTERSECT
```
SELECT  NULL AS v1
FROM    DUAL

INTERSECT

SELECT  NULL AS v1
FROM    DUAL
```
  Query result:
```
v1
------
(null)
```
- Example 4: MINUS
```
SELECT  NULL AS v1
FROM    DUAL

MINUS

SELECT  NULL AS v1
FROM    DUAL
```
  Query result:
```
v1
------
```
NULL Behavior with DISTINCT
The DISTINCT operator follows the same NULL handling rules as set operations. Multiple NULL values are considered identical and are reduced to a single NULL value in the result set.

Example:
```
SELECT DISTINCT a.v1
FROM
(
    SELECT  1 AS v1
    FROM    DUAL

    UNION ALL

    SELECT  NULL AS v1
    FROM    DUAL

    UNION ALL

    SELECT  NULL AS v1
    FROM    DUAL
) a
```
Query result:
```
v1
------
     1
(null)
```
NULL Handling in Grouping and Sorting
Both GROUP BY and ORDER BY clauses treat NULL values consistently with set operations. NULL values are considered equal to each other for grouping purposes and are sorted together in ordering operations.

Grouping behavior: All NULL values are placed in the same group when using GROUP BY.

Sorting behavior: NULL values are typically sorted together, with their position (first or last) depending on the database configuration and specific ORDER BY clause options.

Example:
```
SELECT  a.v1, COUNT(1) AS nbr
FROM
(
    SELECT  1 AS v1
    FROM    DUAL

    UNION ALL

    SELECT  NULL AS v1
    FROM    DUAL

    UNION ALL

    SELECT  NULL AS v1
    FROM    DUAL
) a
GROUP BY v1
ORDER BY v1 DESC
```
Query result:
```
v1     nbr
------ ------
(null)      2
     1      1
```

Quick Reference Summary

Operation	NULL Scenario	Result	Returns Rows
`expr = NULL`	Any comparison with NULL	Always FALSE	No
`expr IS NULL`	Testing for NULL values	TRUE if expr is NULL	Yes (if NULL)
`expr IN (list)`	expr is NULL	Always FALSE	No
`expr IN (list)`	List contains only NULLs	Always FALSE	No
`expr NOT IN (list)`	expr is NULL	Always FALSE	No
`expr NOT IN (list)`	List contains any NULL	Always FALSE	No
`EXISTS (subquery)`	Subquery returns NULLs	TRUE if any rows returned	Yes (if rows exist)
`NOT EXISTS (subquery)`	Subquery returns NULLs	FALSE if any rows returned	No (if rows exist)
`COUNT(column)`	Column contains NULLs	Ignores NULL values	Returns count of non-NULLs
`SUM/MIN/MAX/AVG(column)`	All values are NULL	Returns NULL	Returns NULL result
`DISTINCT`	Multiple NULL values	Treats NULLs as equal	Returns single NULL
`GROUP BY`	NULL values in grouping column	Groups all NULLs together	Creates NULL group
`Set Operations`	UNION/INTERSECT/MINUS with NULLs	Treats NULLs as equal	Handles NULLs consistently