Oracle Multiset Operators

Multiset operators combine the results of two nested tables into a single nested table.

The examples related to multiset operators require that two nested tables be created and loaded with data as follows:

First, make a copy of the test table called customers_demo:

Next, create a table type called cust_address_tab_typ. This type will be used when creating the nested table columns.

Now, create two nested table columns in the customers_demo table:

Finally, load data into the two new nested table columns using data from the cust_address column of the oe.customers table:

MULTISET EXCEPT

MULTISET EXCEPT takes as arguments two nested tables and returns a nested table whose elements are in the first nested table but not in the second nested table. The two input nested tables must be of the same type, and the returned nested table is of the same type as well.

nested_table1
MULTISET EXCEPT [ ALL | DISTINCT ]
nested_table2

• The ALL keyword instructs Oracle to return all elements in nested_table1 that are not in nested_table2. For example, if a particular element occurs m times in nested_table1 and n times in nested_table2, then the result will have (m-n) occurrences of the element if m >n and 0 occurrences if m<=n. ALL is the default.
• The DISTINCT keyword instructs Oracle to eliminate any element in nested_table1 which is also in nested_table2, regardless of the number of occurrences.
• The element types of the nested tables must be comparable. Refer to "Comparison Conditions" for information on the comparability of nonscalar types.

Example

The following example compares two nested tables and returns a nested table of those elements found in the first nested table but not in the second nested table:

The preceding example requires the table customers_demo and two nested table columns containing data. Refer to "Multiset Operators" to create this table and nested table columns.

MULTISET INTERSECT

MULTISET INTERSECT takes as arguments two nested tables and returns a nested table whose values are common in the two input nested tables. The two input nested tables must be of the same type, and the returned nested table is of the same type as well.

nested_table1
MULTISET INTERSECT [ ALL | DISTINCT ]
nested_table2

• The ALL keyword instructs Oracle to return all elements in nested_table1 that are not in nested_table2. For example, if a particular element occurs m times in nested_table1 and n times in nested_table2, then the result will have (m-n) occurrences of the element if m >n and 0 occurrences if m<=n. ALL is the default.
• The DISTINCT keyword instructs Oracle to eliminate any element in nested_table1 which is also in nested_table2, regardless of the number of occurrences.
• The element types of the nested tables must be comparable. Refer to "Comparison Conditions" for information on the comparability of nonscalar types.

Example

The following example compares two nested tables and returns a nested table of those elements found in the first nested table but not in the second nested table:

The preceding example requires the table customers_demo and two nested table columns containing data. Refer to "Multiset Operators" to create this table and nested table columns.

MULTISET INTERSECT

MULTISET INTERSECT takes as arguments two nested tables and returns a nested table whose values are common in the two input nested tables. The two input nested tables must be of the same type, and the returned nested table is of the same type as well.

nested_table1
MULTISET INTERSECT [ ALL | DISTINCT ]
nested_table2

• The ALL keyword instructs Oracle to return all common occurrences of elements that are in the two input nested tables, including duplicate common values and duplicate common NULL occurrences. For example, if a particular value occurs m times in nested_table1 and n times in nested_table2, then the result would contain the element min(m,n) times. ALL is the default.
• The DISTINCT keyword instructs Oracle to eliminate duplicates from the returned nested table, including duplicates of NULL, if they exist.
• The element types of the nested tables must be comparable. Refer to "Comparison Conditions" for information on the comparability of nonscalar types.

Example

The following example compares two nested tables and returns a nested table of those elements found in both input nested tables:

The preceding example requires the table customers_demo and two nested table columns containing data. Refer to "Multiset Operators" to create this table and nested table columns.

MULTISET UNION

MULTISET UNION takes as arguments two nested tables and returns a nested table whose values are those of the two input nested tables. The two input nested tables must be of the same type, and the returned nested table is of the same type as well.

nested_table1
MULTISET UNION [ ALL | DISTINCT ]
nested_table2

• The ALL keyword instructs Oracle to return all elements that are in the two input nested tables, including duplicate values and duplicate NULL occurrences. This is the default.
• The DISTINCT keyword instructs Oracle to eliminate duplicates from the returned nested table, including duplicates of NULL, if they exist.
• The element types of the nested tables must be comparable. Refer to "Comparison Conditions" for information on the comparability of nonscalar types.

Example

The following example compares two nested tables and returns a nested table of elements from both input nested tables:

The preceding example requires the table customers_demo and two nested table columns containing data. Refer to "Multiset Operators" to create this table and nested table columns.