Oracle PL/SQL Programming, 5th Edition

Nested Table Multiset Operations

The essential advance made in collections starting with Oracle Database 10g is that the database treats nested tables more like the multisets that they actually are. The database provides high-level set operations that can be applied to nested tables and only, for the time being, to nested tables. Here is a brief summary of these set-level capabilities:

Operation	Return value	Description
=	BOOLEAN	Compares two nested tables, and returns TRUE if they have the same named type and cardinality and if the elements are equal.
<> or !=	BOOLEAN	Compares two nested tables, and returns FALSE if they differ in named type, cardinality, or equality of elements.
[NOT] IN ()	BOOLEAN	Returns TRUE [FALSE] if the nested table to the left of IN exists in the list of nested tables in the parentheses.
x MULTISET EXCEPT [DISTINCT] y	NESTED TABLE	Performs a MINUS set operation on nested tables x and y, returning a nested table whose elements are in x, but not in y. x, y, and the returned nested table must all be of the same type. The DISTINCT keyword instructs Oracle to eliminate any element in x which is also in y, regardless of the number of occurrences.
x MULTISET INTERSECT [DISTINCT] y	NESTED TABLE	Performs an INTERSECT set operation on nested tables x and y, returning a nested table whose elements are in both x and y. x, y, and the returned nested table must all be of the same type. The DISTINCT keyword forces the elimination of duplicates from the returned nested table, including ...