The MULTISET UNION operator combines two collections to form a third collection. The signature is:
Where coll_1 and coll_2 specify the names of the collections to combine.
Include the ALL keyword to specify that duplicate elements (elements that are present in both coll_1 and coll_2) should be represented in the result, once for each time they are present in the original collections. This is the default behavior of MULTISET UNION.
Include the DISTINCT or UNIQUE keyword to specify that duplicate elements should be included in the result only once. The DISTINCT and UNIQUE keywords are synonymous.
The following example demonstrates using the MULTISET UNION operator to combine two collections (collection_1 and collection_2) into a third collection (collection_3):
The resulting collection includes one entry for each element in collection_1 and collection_2. If the DISTINCT keyword is used, the results are as follows:
The resulting collection includes only those members with distinct values. Note in the following example that the MULTISET UNION DISTINCT operator also removes duplicate entries that are stored within the same collection: