SQL Multisets
MULTISET Defined
A 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 Types
The 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 Declaration
All 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.
CREATE TABLE mstable (name VARCHAR(10), favfruits VARCHAR(5) MULTISET);
MULTISET Value Constructors
There 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
INSERT INTO mstable
VALUES('Wael', MULTISET['Apple', 'Pear']);
This says, Wael's favorite fruits are Apple and Pear.
Alternatively the SQL standard also permits the same construct with the bracket trigraphs ??( and ??)
INSERT INTO mstable
VALUES('Wael', MULTISET??('Apple', 'Pear'??));
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
INSERT INTO mstable
VALUES('Wael', MULTISET['Apple', SELECT*FROM fruits WHERE latin_name='Pirus malus L.']);
Value by Query
If you like all the fruit there are, SQL provides the following construct
INSERT INTO mstable
VALUES('You', MULTISET(SELECT english_name FROM fruits));
Which will create a multiset based on the table fruits. As syntactic sugar, SQL allows
INSERT INTO mstable
VALUES('You', TABLE(SELECT english_name FROM fruits));
MULTISET UNNEST
Going in the other direction, it is possible to retrieve all the values of a multiset as a series of rows.
-- Returns two rows. First row 'Apple', Second row 'Pear'
SELECT * FROM UNNEST(SELECT favfruits FROM mstable WHERE name = 'Wael');
MULTISET ELEMENT
Similar, but not quite the same as UNNEST is ELEMENT which converts a multiset with cardinality 1 to a row expression.
-- Find all persons and their one and only fruit they like
SELECT name, ELEMENT(favfruits)
FROM mstable
WHERE CARDINALITY(favfruits) = 1;
This was a sneak peak on CARDINALITY which is up next.
MULTISET Cardinality
As 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
-- Returns 7
SELECT CARDINALITY(MULTISET[3,5,7,11,13,17,19]);
MULTISET Predicates
MULTISET MEMBER OF
To test if an element is contained in a multiset, the predicate MEMBER OF is used.
-- Find all people who like apples
SELECT name
FROM mstable
WHERE 'Apple' MEMBER OF favfruits;
-- Returns true
SELECT 'Apple' MEMBER OF (SELECT favfruits FROM mstable WHERE name='Wael');
MULTISET IS A SET
IS A SET can be used to test if a multiset contains duplicate entries.
-- Returns true
VALUES MULTISET['Apple', 'apple'] IS A SET;
-- Returns false
VALUES MULTISET['Apple', 'Banana', 'Citron', 'Apple'] IS A SET;
MULTISET SUB MULTISET OF
SUB MULTISET OF tests if one multiset is a subset of another multiset.
-- Who likes apples *AND* bananas?
SELECT name FROM mstable
WHERE MULTISET['Apple', 'Banana'] SUB MULTISET OF favfruits;
MULTISET SET OPERATORS
The 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 UNION
MULTISET UNION syntax:
SELECT ms1 MULTISET UNION ms2 FROM T;
SELECT ms1 MULTISET UNION ALL ms2 FROM T;
SELECT ms1 MULTISET UNION DISTINCT ms2 FROM T;
MULTISET INTERSECT
MULTISET INTERSECT syntax:
SELECT ms1 MULTISET INTERSECT ms2 FROM T;
SELECT ms1 MULTISET INTERSECT ALL ms2 FROM T;
SELECT ms1 MULTISET INTERSECT DISTINCT ms2 FROM T;
MULTISET EXCEPT
MULTISET EXCEPT syntax:
SELECT ms1 MULTISET EXCEPT ms2 FROM T;
SELECT ms1 MULTISET EXCEPT ALL ms2 FROM T;
SELECT ms1 MULTISET EXCEPT DISTINCT ms2 FROM T;
MULTISET AGGREGATES
The 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:| NAME | FAVFRUITS |
|---|---|
| Wael | MULTISET['Apple','Pear'] |
| Adnan | MULTISET['Water Melon','Blueberry','Apple'] |
| Saleh | MULTISET['Blueberry','Apple','Prune'] |
MULTISET COLLECT
COLLECT 'collects' all of its input and outputs one single multiset containing all of the collected inputs. For example
SELECT COLLECT(name) as ALL_PEOPLE FROM mstable;
| ALL_PEOPLE |
|---|
| MULTISET['Wael','Adnan','Saleh'] |
MULTISET FUSION
FUSION 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.
SELECT FUSION(favfruits) as ALL_LIKED_FRUITS FROM mstable;
Note the ordering of the elements in the presented resulting multiset. The standard doesn't specify multiset to be ordered in any way.
| ALL_LIKED_FRUITS |
|---|
| MULTISET['Apple','Pear','Water Melon','Blueberry','Blueberry','Prune','Apple','Apple'] |
MULTISET INTERSECTION
In our fruity example, INTERSECTION figures out the common fruit everyone likes
SELECT INTERSECTION(favfruits) as COMMON_FRUITS FROM mstable;
| COMMON_FRUITS |
|---|
| MULTISET['Apple'] |
As with other aggregates, it is not allowed to mix non aggregate and multiset aggregate calls without a GROUP BY.
Posted by Wael Chatila on May 18, 2005 11:55:43 PM PDT
#
digg 
del.icio.us
technorati
[more]