To evaluate set operations such as union, difference or intersection for SET, MULTISET or LIST (SEQUENCE) types, you can use +, - or * operators respectively.
The following table shows a summary of how to use these operators.
Result Data Type by Operand Type
|
SET |
MULTISET |
LIST |
---|---|---|---|
SET |
+, -, * : SET |
+, -, * : MULTISET |
+, -, * : MULTISET |
MULTISET |
+, -, * : MULTISET |
+, -, * : MULTISET |
+, -, * : MULTISET |
LIST |
+ : MULTISET |
+ : MULTISET |
+ : LIST |
value_expression set_arithmetic_operator value_expression
value_expression :
• collection value
• NULL
set_arithmetic_operator :
• + (union)
• - (difference)
• * (intersection)
SELECT ((CAST ({3,3,3,2,2,1} AS SET))+(CAST ({4,3,3,2} AS MULTISET)));
(( cast({3, 3, 3, 2, 2, 1} as set))+( cast({4, 3, 3, 2} as multiset)))
======================
{1, 2, 2, 3, 3, 3, 4}
SELECT ((CAST ({3,3,3,2,2,1} AS MULTISET))+(CAST ({4,3,3,2} AS MULTISET)));
(( cast({3, 3, 3, 2, 2, 1} as multiset))+( cast({4, 3, 3, 2} as multiset)))
======================
{1, 2, 2, 2, 3, 3, 3, 3, 3, 4}
SELECT ((CAST ({3,3,3,2,2,1} AS LIST))+(CAST ({4,3,3,2} AS MULTISET)));
(( cast({3, 3, 3, 2, 2, 1} as sequence))+( cast({4, 3, 3, 2} as multiset)))
======================
{1, 2, 2, 2, 3, 3, 3, 3, 3, 4}
SELECT ((CAST ({3,3,3,2,2,1} AS SET))-(CAST ({4,3,3,2} AS MULTISET)));
(( cast({3, 3, 3, 2, 2, 1} as set))-( cast({4, 3, 3, 2} as multiset)))
======================
{1}
SELECT ((CAST ({3,3,3,2,2,1} AS MULTISET))-(CAST ({4,3,3,2} AS MULTISET)));
(( cast({3, 3, 3, 2, 2, 1} as multiset))-( cast({4, 3, 3, 2} as multiset)))
======================
{1, 2, 3}
SELECT ((CAST ({3,3,3,2,2,1} AS LIST))-(CAST ({4,3,3,2} AS MULTISET)));
(( cast({3, 3, 3, 2, 2, 1} as sequence))-( cast({4, 3, 3, 2} as multiset)))
======================
{1, 2, 3}
SELECT ((CAST ({3,3,3,2,2,1} AS SET))*(CAST ({4,3,3,2} AS MULTISET)));
(( cast({3, 3, 3, 2, 2, 1} as set))*( cast({4, 3, 3, 2} as multiset)))
======================
{2, 3}
SELECT ((CAST ({3,3,3,2,2,1} AS MULTISET))*(CAST ({4,3,3,2} AS MULTISET)));
(( cast({3, 3, 3, 2, 2, 1} as multiset))*( cast({4, 3, 3, 2} as multiset)))
======================
{2, 3, 3}
SELECT ((CAST ({3,3,3,2,2,1} AS LIST))*(CAST ({4,3,3,2} AS MULTISET)));
(( cast({3, 3, 3, 2, 2, 1} as sequence))*( cast({4, 3, 3, 2} as multiset)))
======================
{2, 3, 3}
For a collection value to be assigned to a variable, the outer query must return a single row as the result. The following example shows how to assign a collection value to a variable. The outer query must return only a single row as follows:
SELECT SET(SELECT name
FROM people
WHERE ssn in {'1234', '5678'})
TO :"names"
FROM TABLE people;