Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - SQL Reference » SQL statements » Alphabetical list of SQL statements

UNION statement

Combines the results of two or more SELECT statements or query expressions.

[ WITH temporary-views ] query-block
  UNION [ ALL | DISTINCT ] query-block
[ ORDER BY [ integer | select-list-expression-name ] [ ASC | DESC ], ... ]
[ FOR XML xml-mode ]
[ OPTION( query-hint, ... ) ]
temporary-views :
  regular-view, ...
| RECURSIVE { regular-view | recursive-view }, ...
regular-view :
  view-name [ ( column-name, ... ) ]
  AS ( query-block )
recursive-view :
  view-name ( column-name, ... )
  AS ( initial-query-block UNION ALL recursive-query-block )
query-block : see the documentation on common elements in SQL syntax
query-hint :
| option-name = option-value
option-name : identifier
option-value : 
hostvar (indicator allowed)
| string
| identifier
| number

    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.

      manager ( EmployeeID, ManagerID,
                GivenName, Surname, mgmt_level ) AS
    ( ( SELECT EmployeeID, ManagerID,       -- initial subquery
               GivenName, Surname, 0
        FROM Employees AS e
        WHERE ManagerID = EmployeeID )
      ( 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
    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
    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:

    • 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.

Side effects


  • 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.


List all distinct surnames of employees and customers.

SELECT Surname
SELECT Surname
FROM GROUPO.Customers;