MULTISET DefinedA multiset is an unordered collection type containing zero or an implementation defined max amount of elements. All elements must be of the same type. Same element can occur in the multiset multiple times.
MULTISET Element TypesThe element type can be any valid SQL type. This means elements in a MULTISET can be any of the following
- User Defined Types (UDT)
- ROW types
- Another MULTISET
- All other built in types such as int, double, varchar etc
MULTISET DeclarationAll you have to do in order to declare a multiset type is to add 'MULTISET' after the element type in the declaration. Below is an example of a table representing people and their favorite fruits.
MULTISET Value ConstructorsThere are three ways to create multisets. We will use our favorite fruit table defined above to illustrate the use of value constructors.
Value by Enumeration
Alternatively the SQL standard also permits the same construct with the bracket trigraphs ??( and ??)
Note, although it can be tempting to think of this construct as a literal one it is possible to, for example, have a value subquery in the enumeration
Value by QueryIf you like all the fruit there are, SQL provides the following construct
MULTISET UNNESTGoing in the other direction, it is possible to retrieve all the values of a multiset as a series of rows.
MULTISET ELEMENTSimilar, but not quite the same as UNNEST is ELEMENT which converts a multiset with cardinality 1 to a row expression.
MULTISET CardinalityAs opposed to the SQL ARRAY type, multiset doesn't have any max cardinality restriction and is implementation defined. To find the number of elements in a multiset, use the CARDINALITY operator
MULTISET MEMBER OFTo test if an element is contained in a multiset, the predicate MEMBER OF is used.
MULTISET IS A SETIS A SET can be used to test if a multiset contains duplicate entries.
MULTISET SUB MULTISET OFSUB MULTISET OF tests if one multiset is a subset of another multiset.
MULTISET SET OPERATORSThe multiset set operators work the same way as for the non multiset ones, but the obvious difference that they work on the elements in the multiset.
MULTISET UNIONMULTISET UNION syntax:
MULTISET INTERSECTMULTISET INTERSECT syntax:
MULTISET EXCEPTMULTISET EXCEPT syntax:
MULTISET AGGREGATESThe available multiset operators wouldn't be complete without multiset enabled aggregates. In the following examples to come we have a table with the following content:
MULTISET COLLECTCOLLECT 'collects' all of its input and outputs one single multiset containing all of the collected inputs. For example
MULTISET FUSIONFUSION works much in a similar way of COLLECT but with the difference that the input is now restricted to be of a MULTISET type. COLLECT operates on any type. FUSION collects all the elements of the multisets in it's input and outputs one single multiset containing all of the collected inputs.
MULTISET INTERSECTIONIn our fruity example, INTERSECTION figures out the common fruit everyone likes
As with other aggregates, it is not allowed to mix non aggregate and multiset aggregate calls without a GROUP BY.