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 Dynamic Server, 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.

Related Information