Oracle PL/SQL Tutorial/Collections/IN

IN and NOT IN Operators

You can use the IN and NOT IN operators to check if the contents of one nested table appear or don"t appear in the contents of another nested table.

SQL>
SQL> CREATE OR REPLACE PROCEDURE in_example AS
  2    TYPE nestedTableType IS TABLE OF VARCHAR2(10);
  3    myTable1 nestedTableType;
  4    myTable2 nestedTableType;
  5    myTable3 nestedTableType;
  6    result BOOLEAN;
  7  BEGIN
  8    myTable1 := nestedTableType("A", "B", "C");
  9    myTable2 := nestedTableType("D", "E", "F");
 10    myTable3 := nestedTableType("A", "B", "C");
 11
 12    result := myTable3 IN (myTable1);
 13    IF result THEN
 14      DBMS_OUTPUT.PUT_LINE("myTable3 in myTable1");
 15    END IF;
 16
 17    result := myTable3 NOT IN (myTable2);
 18    IF result THEN
 19      DBMS_OUTPUT.PUT_LINE("myTable3 not in myTable2");
 20    END IF;
 21  END in_example;
 22  /
Procedure created.
SQL> CALL in_example();
myTable3 in myTable1
myTable3 not in myTable2
Call completed.
SQL>

NOT IN example (table collection)

SQL> CREATE OR REPLACE PROCEDURE in_example AS
  2    TYPE charTable IS TABLE OF VARCHAR2(10);
  3    emp1 charTable;
  4    emp2 charTable;
  5    emp3 charTable;
  6    result BOOLEAN;
  7  BEGIN
  8    emp1 := charTable("A", "B", "C");
  9    emp2 := charTable("C", "D", "E");
 10    emp3 := charTable("A", "B", "C");
 11
 12
 13
 14    result := emp3 NOT IN (emp2);
 15    IF result THEN
 16      DBMS_OUTPUT.PUT_LINE("emp3 not in emp2");
 17    END IF;
 18  END in_example;
 19  /
Procedure created.
SQL>