MTI TEK
  • Home
  • About
  • LLMs
  • Docker
  • Kubernetes
  • Java
  • All Resources
Oracle SQL | Handling NULL Values in Oracle SQL
  1. Understanding NULL Values
  2. NULL Behavior with Aggregation Functions
  3. NULL Handling in Conditional Operators
    1. Operator: IN
      1. Standard matching behavior
      2. Behavior with all NULL values or empty lists
      3. Behavior when expression is NULL
    2. Operator: NOT IN
      1. Behavior with NULL values in list
      2. Behavior when expression is NULL
      3. Behavior with empty lists
  4. NULL Behavior with Existence Operators
    1. Operator: EXISTS
      1. True condition behavior
      2. False condition behavior
    2. Operator: NOT EXISTS
      1. True condition behavior
  5. NULL Handling in Set Operations
  6. NULL Behavior with DISTINCT
  7. NULL Handling in Grouping and Sorting
  8. Quick Reference Summary

  1. 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
      
  2. 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
      
  3. NULL Handling in Conditional Operators
    The IN and NOT IN operators have complex behavior when dealing with NULL values.
    1. 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.
      1. 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
          
      2. 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
          --
          
      3. 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
          --
          
    2. 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.
      1. 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
          --
          
      2. 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
          --
          
      3. 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
          
  4. 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.
    1. 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.
      1. 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
          
      2. 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
          --
          
    2. 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.
      1. 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
          
  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
      ------
      
  6. 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)
    
  7. 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
    
  8. 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
© 2025 mtitek