PL/SQL Multiset operator tips

The multiset operators are a bunch in number, which combines two or more collection types of the same type and returns a collection result of the same type that is appropriate to the operator used. These perform a similar operation to that of the SQL set operators, where the former works only on the collection types and the latter on the SQL SELECT statements.

Multiset operators with their SQL equivalents

Collection SET Operator	Equivalent SQL SET Operator
Multiset Union	Union all
Multiset Union all	Union all
Multiset Union Distinct	Union
Multiset Intersect	NA
Multiset Intersect Distinct	Intersect
Multiset Except	NA
Multiset Except Distinct	Minus

The below nested table type is created as a persistent object for explaining the different multiset operators.

CREATE OR REPLACE type type_ntt

IS

  TABLE OF VARCHAR2(1);

/

Multiset Union [All]

The multiset union operator evaluates two collection instances and returns a single instance, which contains all the elements from the input two instances. If there are any duplicate elements found, they are returned as such. This operator is identical to the multiset union alloperator. This operator is equivalent to the union all operator in SQL.

In the below example, two instances of a single nested type are created. The first instance is assigned to A, B, C and D characters and the second instance is assigned to A and B characters. When they are operated, they just append the elements of the second nested table type instance of the first one.

WITH

  FUNCTION func_set RETURN type_ntt

IS

 l_ntt_var1 type_ntt :=type_ntt('A', 'B', 'C', 'D');

 l_ntt_var2 type_ntt :=type_ntt('A', 'B');

BEGIN

  RETURN l_ntt_var1 multiset UNION l_ntt_var2;

END;

SELECT * FROM TABLE(func_set);

/

Result:

A

B

C

D

A

B

Multiset Union Distinct

This operator works similar to the multiset union operator along with the duplication elements removal. This operator is equivalent to the union operator in SQL. The distinct keyword used in this operator cannot be replaced with its synonymous keyword unique.

% Note: The result returned by this operator is notsorted, unlike the SQL union operator.

In the below example, the functional with clause has two instances with a list of values assigned to them. The TABLE function in the select statement returns the total list of elements from the two instances removing the duplication without sorting them.

WITH

  FUNCTION func_set RETURN type_ntt

IS

 l_ntt_var1 type_ntt :=type_ntt('E', 'B', 'C', 'D');

 l_ntt_var2 type_ntt :=type_ntt('A', 'B');

BEGIN

  RETURN l_ntt_var1 multiset UNION Distinct l_ntt_var2;

END;

SELECT * FROM TABLE(func_set);

/

Result:

E

B

C

D

A

Multiset Intersect

The multiset intersect operator evaluates two collection instances of the same type and returns the common elements in them without any duplication removal. This operator does not have an equivalent SQL set operator.

In the below listing, the two instances of the nested table type are created and assigned to a list of values to them. The TABLE function returns the common duplicated unsorted elements from two instances.

WITH

  FUNCTION func_set RETURN type_ntt

IS

 l_ntt_var1 type_ntt :=type_ntt('A', 'A', 'C', 'D');

 l_ntt_var2 type_ntt :=type_ntt('A', 'A', 'B');

BEGIN

  RETURN l_ntt_var1 multiset intersect l_ntt_var2;

END;

SELECT * FROM TABLE(func_set);

/

Result:

A

A

Multiset Intersect Distinct

This operator works similar to the multiset intersect operator explained in the above section with additional duplicate element removal. In the below example, the operator works on the two instances loaded with a list of values with each other and return common unique unsorted element values using the TABLE function. Usually, the equivalent SQL set operator sorts their results, unlike this collection operator. The distinct keyword used in this operator cannot be replaced with its synonymous keyword unique.

WITH

  FUNCTION func_set RETURN type_ntt

IS

 l_ntt_var1 type_ntt :=type_ntt('B', 'B', 'A', 'A');

 l_ntt_var2 type_ntt :=type_ntt('A', 'A', 'C', 'C', 'B', 'B');

BEGIN

  RETURN l_ntt_var1 multiset intersect distinct l_ntt_var2;

END;

SELECT * FROM TABLE(func_set);

/

Result:

B

A

Multiset Except

The multiset except operator evaluates two similar collection instances and returns the remaining elements from the first instance after removing any matching elements from the second instance as its resultant. This operator does not have an SQL equivalent.

In the below example, the two instances with a list of elements are operated on the multiset except operator and returns the uncommon elements from the first instance without performing any duplication removal or sorting.

WITH

  FUNCTION func_set RETURN type_ntt

IS

 l_ntt_var1 type_ntt :=type_ntt('C', 'C', 'B', 'A', 'D');

 l_ntt_var2 type_ntt :=type_ntt('D', 'A');

BEGIN

  RETURN l_ntt_var1 multiset except l_ntt_var2;

END;

SELECT * FROM TABLE(func_set);

/

Result:

C

C

B

Multiset Except Distinct

This operator works similar to the multiset except operator but performs an additional duplication removal operation to the result. This operator still does not sort its resultant, unlike its SQL equivalent. The distinct keyword used in this operator cannot be replaced with its synonymous keyword unique.

In the below example, the two instances with a list of elements are operated on this operator to return the uncommon elements list from the first instance with duplication check and no sorting.

WITH

  FUNCTION func_set RETURN type_ntt

IS

 l_ntt_var1 type_ntt :=type_ntt('C','C', 'B', 'A', 'D');

 l_ntt_var2 type_ntt :=type_ntt('D', 'A');

BEGIN

  RETURN l_ntt_var1 multiset except distinct l_ntt_var2;

END;

SELECT * FROM TABLE(func_set);

/

Result:

C

B