Restrictions on a Combined SELECT

Several restrictions apply to queries that you can combine with the UNION, INTERSECT, MINUS, or EXCEPT set operators.

  • The number of items in the Projection clause of each query must be the same, and the corresponding items in each Projection clause must have compatible data types.
  • The Projection clause of each query cannot specify BYTE or TEXT objects. (This restriction does not apply to UNION ALL operations.)
  • If a combined query includes the ORDER BY clause of the SELECT statement, it must follow the last Projection clause, and you must specify each ordered item by its integer select_number, not by its SQL identifier. Sorting takes place after the set operation is complete.
  • You can store the combined results of any set operator in a temporary table, but the INTO TEMP clause can appear only in the final SELECT statement.
  • In Informix® ESQL/C, you cannot use an INTO clause in a compound query unless exactly one row is returned, and you are not using a cursor. In this case, the INTO clause must be in the first SELECT statement that the set operator combines.
A UNION subquery is a query that includes the UNION or UNION ALL operator within a subquery. The following additional restrictions affect UNION subqueries, but they do not apply to combined queries that include the INTERSECT, MINUS, or EXCEPT set operators:
  • The CREATE VIEW statement cannot specify a UNION subquery to define the view.
  • Only columns in the local database are valid in a UNION subquery. You cannot reference a remote table or view in a UNION subquery.
Unlike UNION, the INTERSECT, MINUS, and EXCEPT set operators are valid in the following contexts:
  • In combined queries that reference columns of tables in other databases of the local Informix® server instance, and in tables of other Informix® server instances.
  • In view definitions. (You cannot, however, specify WITH CHECK OPTION in a CREATE VIEW statement that also includes a set operator.)
The following restrictions, however, affect all combined queries, including UNION and UNION ALL subqueries and queries that include the INTERSECT, MINUS, or EXCEPT set operators:
  • Combined queries cannot be triggering events. If a valid combined query specifies a column on which a Select trigger has been defined, the query succeeds, but the trigger (or the INSTEAD OF trigger on a view) is ignored.
  • General expressions that include host variables are not valid on the left of the ALL, ANY, IN, NOT IN and SOME operators in a query that includes a UNION subquery or any other set operator. An expression that consists solely of a single host variable, however, is valid in this context.
For example, the following query is valid under the above restriction: 
SELECT col1 FROM tab1 WHERE ?  <= ALL
   (SELECT col2 FROM tab2 UNION SELECT col3 FROM tab3);

In this example, the expression to the left of ALL is a single host variable ( ? ), which is the only expression involving host variables that is supported before the ALL, ANY, IN, NOT IN, or SOME operators in a query that also includes a set operator.

In contrast, the following example shows an invalid query: 
SELECT col1 FROM tab1 WHERE (? + 8) <= ALL
   (SELECT col2 FROM tab2 UNION SELECT col3 FROM tab3);

This query fails because an operand of the <= relational operator to the left of the ALL operator is (? + 8). An arithmetic expression that includes a host variable is not valid syntax in a UNION subquery, nor in queries that are combined by any other set operator.

Expressions that do not contain host variables are not subject to this restriction. Thus, the following query (that includes the same UNION subquery) is valid: 
SELECT col1 FROM tab1 WHERE (col1 + 8) <= ALL
   (SELECT col2 FROM tab2 UNION SELECT col3 FROM tab3);