Collection subqueries in the FROM clause

Collection subqueries are valid in the FROM clause of SELECT statements, where the outer query can use the values returned by the subquery as a source of data.

The query examples in the section Collection subqueries specify collection subqueries by using the TABLE keyword followed (within parentheses) by the MULTISET keyword, followed by a subquery. This syntax is the HCL Informix® extension to the ANSI/ISO standard for the SQL language.

In the FROM clause of the SELECT statement, and only in that context, you can substitute syntax that complies with the ANSI/ISO standard for SQL by specifying a subquery, omitting the TABLE and MULTISET keywords and the nested parentheses, to specify a collection subquery.

The following query uses the HCL Informix extension syntax to join two collection subqueries in the FROM clause of the outer query:

Figure 1. Query

SELECT * FROM TABLE(MULTISET(SELECT SUM(C1) FROM T1 GROUP BY C1)),
         TABLE(MULTISET(SELECT SUM(C1) FROM T2 GROUP BY C2));

The following logically equivalent query returns the same results as the query above by using ANSI/ISO-compliant syntax to join two derived tables in the FROM clause of the outer query:

Figure 2. Query

SELECT * FROM (SELECT SUM(C1) FROM T1 GROUP BY C1),
              (SELECT SUM(C1) FROM T2 GROUP BY C2);

An advantage of this query over the TABLE(MULTISET(SELECT ...)) HCL Informix extension version is that it can also be executed by any database server that supports the ANSI/ISO-compliant syntax in the FROM clause. For more information about syntax and restrictions for collection subqueries, see the HCL Informix Guide to SQL: Syntax.