Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Register

Versions available for this page: CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 |

#### Set Arithmetic Operators

###### Set Arithmetic Operators

To compute union, difference or intersection of collections types (SET, MULTISET, and LIST (SEQUENCE)), you can use +, -, or * operators, respectively. The following table shows a result data type by operator in case that collection type is an operand.

Result Data Type by Operand Type

SET

MULTISET

LIST
(=SEQUENCE)

SET

+, -, *: SET

+, -, *: MULTISET

+, -, *: MULTISET

MULTISET

+, -, *: MULTISET

+, -, *: MULTISET

+, -, *: MULTISET

LIST
(=SEQUENCE)

+, -, *: MULTISET

+, -, *: MULTISET

+: LIST
-, *: MULTISET

###### Syntax

value_expression set_arithmetic_operator value_expression

value_expression:

collection value

NULL

set_arithmetic_operator:

+ (union)

- (difference)

* (intersection)

###### Example

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}

###### Assigning Collection Value to Variable

For a collection value to be assigned to a variable, the outer query must return a single row as a 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;