SQL Reference - SUBMULTISET

SUBMULTISET

SUBMULTISET is a SQL operator used to validate whether a nested table is a sub-multiset of another nested table. This implies that all elements of the first nested table are available in the second nested table, and the frequency of each element in the first one does not exceed its frequency in the second one. This operator can be used in a WHERE clause to filter rows based on nested table contents. The returned result is either true or false.

Example

DECLARE
   TYPE num_tab IS TABLE OF NUMBER;
   n num_tab := num_tab(1, 2, 3, 4, 5);
   m num_tab := num_tab(3, 4, 5, 6, 7);
BEGIN
   IF m SUBMULTISET OF n THEN
      DBMS_OUTPUT.PUT_LINE('m is a submultiset of n');
   ELSE
      DBMS_OUTPUT.PUT_LINE('m is not a submultiset of n');
   END IF;
END;

Output

m is not a submultiset of n

Explanation

The SUBMULTISET function is used to determine if one nested table is a subset of another. In the above example, m is not a submultiset of n because m contains elements 6 and 7 which are not in n. The output confirms this, showing m is not a submultiset of n.