Collection Subquery:
 
    (1)
|---------MULTISET--(--+-subquery----------------------+--)-----|
                       '-SELECT ITEM--singleton_select-'
 

1. Informix extension

The MULTISET and SELECT ITEM keywords have the following significance:

• MULTISET specifies a collection of elements that can contain duplicate values, but that has no specific order of elements.
• SELECT ITEM supports only one expression in the projection list. You cannot repeat the SELECT keyword in the singleton subquery.

You can use a collection subquery in the following contexts:

• The Projection clause and WHERE clause of the SELECT statement
• The VALUES clause of the INSERT statement
• The SET clause of the UPDATE statement
• Wherever you can use a collection expression (that is, any expression that evaluates to a single collection)
• As an argument passed to a user-defined routine

The following restrictions apply to a collection subquery:

• The Projection clause cannot contain duplicate column (field) names.
• It cannot contain aliases for table names. (But it can use aliases for column (field) names, as in some of the examples that follow. )
• It is read-only.
• It cannot be opened twice.
• It cannot contain NULL values.
• It cannot contain syntax that attempts to seek within the subquery.

A collection subquery returns a multiset of unnamed ROW data types. The fields of this ROW type are elements in the projection list of the subquery. Examples that follow access the tables and the ROW types that these statements define:

CREATE ROW TYPE rt1 (a INT);
CREATE ROW TYPE rt2 (x int, y rt1);
CREATE TABLE tab1 (col1 rt1, col2 rt2);
CREATE TABLE tab2 OF TYPE rt1;
CREATE TABLE tab3 (a ROW(x INT));

The following examples of collection subqueries return the MULTISET collections that are listed to the right of the subquery.

The following is another collection subquery:

SELECT f(MULTISET(SELECT * FROM tab1 WHERE tab1.x = t.y))
   FROM t WHERE t.name = 'john doe';

The following collection subquery includes the UNION operator:

SELECT f(MULTISET(SELECT id FROM tab1 
UNION
SELECT id FROM tab2 WHERE tab2.id2 = tab3.id3)) FROM tab3;