Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

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

Set Operators

Set Arithmetic Operators

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.

Collection Operand

Set Arithmetic Operator

Collection Operand

Result Collection Type

SET

+
-
*

SET

SET

SET

+
-
*

MULTISET
LIST (SEQUENCE)

MULTISET

MULTISET

+
-
*

SET
MULTISET
LIST (SEQUENCE)

MULTISET

LIST (SEQUENCE)

+

LIST (SEQUENCE)

LIST (SEQUENCE)

LIST (SEQUENCE)

+

SET
MULTISET

MULTISET

LIST (SEQUENCE)

-
*

SET
MULTISET
LIST (SEQUENCE)

MULTISET

  • Union
  • The + operator joins all elements in two collections to generate a single collection. If you combine two LISTs with the + operator, a concatenated LIST is returned as the result. The individual order of the input LISTs does not change. If the operands of the + operator are all of the same collection type, the result becomes the input collection type. For example, if you add two SETs, a SET is returned as a result, and duplicate elements are removed as can be expected of SETs.
    When computing the union of two MULTISETs, a MULTISET is returned with all the elements sorted in order. For example, the expression {1, 3, 4, 3} + {3, 5, 4} returns a MULTISET {1, 3, 3, 3, 4, 4, 5}. If two different collection types are given, both operands are converted to MULTISETs before calculation, which returns a MULTISET as the result.
  • Difference
  • The - operator is used to identify only elements of a collection that do not belong to another collection. Calculating the set difference between two collections returns the elements in the collection on the left of the - operator that do not belong to the collection on the right. For example, the expression {1, 3, 4, 3} - {3, 5, 4} returns a MULTISET {1, 3}. A SET is returned only when both operands are of the SET type. Otherwise, a MULTISET is returned.
  • Intersection
  • The * operator is used to identify elements that belong to both collections. If both collection operands are of the SET type, a SET type collection is returned as the result with all the duplicates removed. Except for this case, all other combinations of operands return a MULTISET as the result. In such cases, both operands are converted to MULTISETs before calculation, which returns a MULTISET as the result. For example, the expression {1, 3, 4, 3} * {3, 5, 4} returns a MULTISET {3, 4}.
Type Casting

Arithmetic operations on collection data types comply with the following data type casting rules:

  • If two arguments are of the same type, type casting is not necessary; the same type as the arguments is returned as the result.
  • If two arguments are of different collection types, both arguments are cast to MULTISETs if necessary. The result is a MULTISET.
Assigning Collection Value to Variable

For a collection value to be assigned to a variable, the outer query must return a single row as the result.

The following is an example of assigning 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;