RSS RSS feed | Atom Atom feed
Popular Articles: Tom Riddle's Magical Diary | AJAX Lego Robot | AJAX CAPTCHA | SQL Multisets

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:
NAMEFAVFRUITS
WaelMULTISET['Apple','Pear']
AdnanMULTISET['Water Melon','Blueberry','Apple']
SalehMULTISET['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;
ALL_LIKED_FRUITS
MULTISET['Apple','Pear','Water Melon','Blueberry','Blueberry','Prune','Apple','Apple']
Note the ordering of the elements in the presented resulting multiset. The standard doesn't specify multiset to be ordered in any way.

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.

slashdot digg del.icio.us technorati [more]



Re: SQL Multisets

Wow, you really know a lot about multisets! There's a half-finished implementation here; maybe you could help finish it: http://farrago.sf.net

Add a comment Send a TrackBack