UNION Operator

Place the UNION operator between two SELECT statements to combine the queries into a single query.

You can string several SELECT statements together using the UNION operator. Corresponding items do not need to have the same name. Omitting the ALL keyword excludes duplicate rows.

UNION ALL operator

If you use the UNION ALL operator, all the qualifying rows from both queries are returned, without excluding any duplicate rows. (If you combine two queries by using the UNION operator without the ALL keyword, any duplicate rows are removed from the combined set of qualifying rows. That is, if multiple rows contain identical values in the corresponding columns or expressions that the Projection clauses of both queries specify, only one row from each set of duplicates is retained in the result set.)

The next example uses UNION ALL to combine the results of two SELECT statements without removing duplicates. The query returns a list of all the calls that were received during the first quarter of 2007 and the first quarter of 2008. 
SELECT customer_num, call_code FROM cust_calls
   WHERE call_dtime BETWEEN 
          DATETIME (2007-1-1) YEAR TO DAY
      AND DATETIME (2007-3-31) YEAR TO DAY
UNION ALL
SELECT customer_num, call_code FROM cust_calls
   WHERE call_dtime BETWEEN 
          DATETIME (2008-1-1)YEAR TO DAY
      AND DATETIME (2008-3-31) YEAR TO DAY;

If you want to remove duplicates from the result set, use UNION without the keyword ALL as the set operator between the queries. In the preceding example, if the combination 101 B were returned by both SELECT statements, the UNION operator would cause the combination to be listed only once. (If you want to remove duplicates within each SELECT statement, use the DISTINCT or UNIQUE keyword immediately before the Select list of the Projection clause, as described in Controlling duplicate return values.)

The ALL keyword is valid for specifying set operations only with the UNION operator. The database server issues an error if ALL immediately follows the INTERSECT, MINUS, or EXCEPT set operators, which exclude duplicates.

For information on how the database server identifies duplicate NCHAR and NVARCHAR values in databases that have the NLCASE INSENSITIVE property, see the topic NCHAR and NVARCHAR expressions in case-insensitive databases.

UNION in subqueries

You can use the UNION and UNION ALL operators in subqueries of SELECT statements within the WHERE clause, the FROM clause, and in collection subqueries. In this release of Informix®, however, subqueries that include UNION or UNION ALL are not supported in the following contexts:
  • In the definition of a view
  • In the event or in the Action clause of a trigger
  • With the FOR UPDATE clause or with an Update cursor
  • In a distributed query (accessing tables outside the local database)

For more information about collection subqueries, see Collection Subquery. For more information about the FOR UPDATE clause, see FOR UPDATE Clause.

In a combined subquery, the database server can resolve a column name only within the scope of its qualifying table reference. The following query, for example, returns an error: 
SELECT * FROM t1 WHERE EXISTS
   (SELECT a FROM t2
   UNION
   SELECT b FROM t3 WHERE t3.c IN
      (SELECT t4.x FROM t4 WHERE t4.4 = t2.z));

Here t2.z in the innermost subquery cannot be resolved, because z occurs outside the scope of reference of the table reference t2. Only column references that belong to t4, t3, or t1 can be resolved in the innermost subquery. The scope of a table reference extends downwards through subqueries, but not across the UNION operator to sibling SELECT statements.