Oracle PL/SQL - IN Operator with Sets with NULL Values

Introduction

The following code shows what happens when set includes a NULL value.

Demo

SQL>
SQL> CREATE OR REPLACE PROCEDURE print_boolean (
  2    b_name   VARCHAR2,-- w ww .j  ava  2s.c o  m
  3    b_value  BOOLEAN
  4   ) IS
  5  BEGIN
  6    IF b_value IS NULL THEN
  7      DBMS_OUTPUT.PUT_LINE (b_name || ' = NULL');
  8    ELSIF b_value = TRUE THEN
  9      DBMS_OUTPUT.PUT_LINE (b_name || ' = TRUE');
 10    ELSE
 11      DBMS_OUTPUT.PUT_LINE (b_name || ' = FALSE');
 12    END IF;
 13  END;
 14  /

Procedure created.

SQL> DECLARE
  2    a INTEGER; -- Initialized to NULL by default
  3    b INTEGER := 10;
  4    c INTEGER := 100;
  5  BEGIN
  6    print_boolean ('100 IN (NULL, b, c)', 100 IN (a, b, c));
  7    print_boolean ('100 NOT IN (NULL, b, c)', 100 NOT IN (a, b, c));
  8
  9    print_boolean ('100 IN (NULL, b)', 100 IN (a, b));
 10    print_boolean ('100 NOT IN (NULL, b)', 100 NOT IN (a, b));
 11
 12    print_boolean ('NULL IN (a, b)', a IN (a, b));
 13    print_boolean ('NULL NOT IN (NULL, b)', a NOT IN (a, b));
 14  END;
 15  /
100 IN (NULL, b, c) = TRUE
100 NOT IN (NULL, b, c) = FALSE
100 IN (NULL, b) = NULL
100 NOT IN (NULL, b) = NULL
NULL IN (a, b) = NULL
NULL NOT IN (NULL, b) = NULL

PL/SQL procedure successfully completed.

SQL>

Related Topic