Handling Collections in SELECT Statements (IDS)

The database server provides the following SQL features to handle collection expressions:

• A collection subquery takes a virtual table (the result of a subquery) and converts it into a collection.

  A collection subquery always returns a collection of type MULTISET. You can use a collection subquery to convert a query result of relational data into a MULTISET collection. For information about the collection data types, see the IBM Informix: Database Design and Implementation Guide.

• A collection-derived table takes a collection and converts it into a virtual table.

  Each element of the collection is constructed as a row in the collection-derived table. You can use a collection-derived table to access the individual elements of a collection.

The collection subquery and collection-derived table features represent inverse operations: the collection subquery converts row values from a relational table into a collection whereas the collection-derived table converts the elements of a collection into rows of a relational table.