- Introduction to CUBRID Manual
- CUBRID SQL Guide
- Operators and Functions
- Relational Operators
- Arithmetic Operators
- Set Operators
- Containment Operators
- CAST Operator
- EXTRACT Operator
- String Function and Operator
- Numeric Functions
- Date/Time Functions
- Data Type Conversion
- Aggregate Functions
- Condition/Comparison Functions and Operators
- User Name Function
- Click Counter Functions
- ROWNUM Function

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 |

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 |
MULTISET |

MULTISET |
+ |
SET |
MULTISET |

LIST (SEQUENCE) |
+ |
LIST (SEQUENCE) |
LIST (SEQUENCE) |

LIST (SEQUENCE) |
+ |
SET |
MULTISET |

LIST (SEQUENCE) |
- |
SET |
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}.

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.

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;

Show Comments (0)