The SQL language has long offered the ability to apply set operations (UNION, INTERSECT, and MINUS) to the result sets of queries. In Oracle Database 10g, you can now use those same high-level, very powerful operators against nested tables (and only nested tables) in your PL/SQL programs and on nested tables declared as columns inside relational tables.

Nested Table Function:

• MULTISET UNION, all elements in both nested table x and nested table y are returned. If tables x and y are both empty then the returned table will be empty.
• MULTISET INTERSECT, elements in nested table x that also appear in nested table y are returned. If tables x and y have no common elements the returned table will be empty.
• MULTISET EXCEPT, elements in nested table x that do not appear in nested table y are returned. If table x has no elements that are not in table y the returned table will be empty.
• SUBMULTISET OF and NOT SUBMULTISET OF compare two nested tables to determine if all elements in the first nested table also appear in second nested table. This function returns a Boolean value TRUE, FALSE or NULL. The key word OF is optional.

The DISTINCT keyword in nested table function (MULTISET UNION, MULTISET INTERSECT, MULTISET EXCEPT) eliminates duplicate elements from the results. Nested table x, y and the returned table must all be of the same type. If either x or y is null, then the returned table is null.