UNION statement

• WITH or WITH RECURSIVE clause

  Define one or more common table expressions, also known as temporary views, to be used elsewhere in the remainder of the statement. These expressions may be non-recursive, or may be self-recursive. Recursive common table expressions may appear alone, or intermixed with non-recursive table expressions, only if the RECURSIVE keyword is specified. Mutually recursive common table expressions are not supported.

  This clause is permitted only if the SELECT query block appears in one of the following locations:

  • Within a top-level SELECT query block including the top-level SELECT query block of a view definition

  • Within a top-level SELECT statement within an INSERT query block

  • Within a nested SELECT query block defining a derived table in any type of SQL statement

  Recursive expressions consist of an initial subquery and a recursive subquery. The initial-query implicitly defines the schema of the view. The recursive subquery must contain a reference to the view within the FROM clause. During each iteration, this reference refers only to the rows added to the view in the previous iteration. The reference must not appear on the null-supplying side of an outer join. A recursive common table expression must not use aggregate functions and must not contain a GROUP BY, ORDER BY, or DISTINCT clause.

  The WITH clause is not supported with remote tables. The WITH clause may also be used in INTERSECT, UNION, and EXCEPT query blocks.

  This functionality is available only in the Watcom SQL dialect.

  WITH RECURSIVE
    manager ( EmployeeID, ManagerID,
              GivenName, Surname, mgmt_level ) AS
  ( ( SELECT EmployeeID, ManagerID,       -- initial subquery
             GivenName, Surname, 0
      FROM Employees AS e
      WHERE ManagerID = EmployeeID )
    UNION ALL
    ( SELECT e.EmployeeID, e.ManagerID,   -- recursive subquery
             e.GivenName, e.Surname, m.mgmt_level + 1
      FROM Employees AS e JOIN manager AS m
       ON   e.ManagerID =  m.EmployeeID
        AND e.ManagerID <> e.EmployeeID
        AND m.mgmt_level < 20 ) )
  SELECT 'Manager', * FROM manager
  WHERE mgmt_level > 0
  UNION ALL
  SELECT 'Employee', * FROM manager
  WHERE mgmt_level = 0
  ORDER BY mgmt_level, Surname, GivenName;
  

• ORDER BY clause

  Specifies the final ordering of the results. The UNION ORDER BY clause uses column names as determined by the first query-block, however table references from the query-block may not be used in the ORDER BY clause.

  For example, if the following statement was run without AS LastName, then specifying ORDER BY Surname would work, but ORDER BY t1.Surname would not:

  SELECT t1.Surname AS LastName
  FROM GROUPO.Employees t1
  UNION
  SELECT t1.Surname
  FROM GROUPO.Customers t1
  ORDER BY LastName;

• FOR XML clause

  The FOR XML clause is documented with the SELECT statement.

• OPTION clause

  Specifies hints for executing the statement. The following hints are supported:

  • MATERIALIZED VIEW OPTIMIZATION option-value
  • FORCE OPTIMIZATION
  • option-name = option-value. A OPTION( isolation_level = ... ) specification in the query text overrides all other means of specifying isolation level for a query.

UNION ALL concatenates the results of the two query blocks into a single (larger) result set. Each query block may be nested. UNION DISTINCT eliminates duplicate rows in the final result. Eliminating duplicates requires extra processing, so UNION ALL should be used instead of UNION where possible. UNION DISTINCT is identical to UNION.

The result sets of the two query-blocks must be UNION-compatible; they must each have the same number of items in their respective SELECT lists, and the types of each expression should be comparable. If corresponding items in two SELECT lists have different data types, the database server chooses a data type for the corresponding column in the result and automatically convert the columns in each query-block appropriately.

The column names displayed are the same column names that are displayed for the first query-block and these names are used to determine the expression names to be matched with the ORDER BY clause. An alternative way of customizing result set column names is to use a common table expression (the WITH clause).

You must be the owner of the objects mentioned in query-block, or have SELECT privilege on the objects you are joining in the union.

None.

• ANSI/ISO SQL Standard

  Core Feature. Specifying the DISTINCT keyword with UNION is optional ANSI/ISO SQL Language Feature T551. Specifying an ORDER BY clause with UNION is ANSI/ISO SQL Language Feature F850. A query-block that contains an ORDER BY clause constitutes ANSI/ISO SQL Feature F851. A query block that contains a row-limit clause (SELECT TOP or LIMIT) comprises optional ANSI/ISO SQL Language Feature F857 or F858, depending on the context. The FOR XML and OPTION clauses are not in the standard.

• Transact-SQL

  UNION and UNION ALL are supported by Adaptive Server Enterprise. The FOR XML and OPTION clauses are not supported.