Rules for set operations

The following rules apply to UNION, EXCEPT, and INTERSECT statements:

  • Same number of items in the select lists   All select lists in the queries must have the same number of expressions (such as column names, arithmetic expressions, and aggregate functions). The following statement is invalid because the first select list is longer than the second:
    SELECT store_id, city, state
       FROM stores
    UNION
       SELECT store_id, city
         FROM stores_east;

  • Data types must match   Corresponding expressions in the SELECT lists must be of the same data type, or an implicit data conversion must be possible between the two data types, or an explicit conversion should be supplied.

    For example, a UNION, INTERSECT, or EXCEPT is not possible between a column of the CHAR data type and one of the INT data type, unless an explicit conversion is supplied. However, a set operation is possible between a column of the MONEY data type and one of the INT data type.

  • Column ordering   You must place corresponding expressions in the individual queries of a set operation in the same order, because the set operators compare the expressions one to one in the order given in the individual queries in the SELECT clauses.

  • Multiple set operations   You can string several set operations together, as in the following example:
    SELECT City AS Cities
       FROM Contacts
       UNION
          SELECT City
             FROM Customers
       UNION
          SELECT City
             FROM Employees;

    For UNION statements, the order of queries is not important. For INTERSECT, the order is important when there are two or more queries. For EXCEPT, the order is always important.

  • Column headings   The column names in the table resulting from a UNION are taken from the first individual query in the statement. If you want to define a new column heading for the result set, you can do so in the select list of the first query, as in the following example:
    SELECT City AS Cities
       FROM Contacts
       UNION
          SELECT City
             FROM Customers;

    In the following query, the column heading remains as City, as it is defined in the first query of the UNION clause.

    SELECT City
       FROM Contacts
       UNION
          SELECT City AS Cities
             FROM Customers;

    Alternatively, you can use the WITH clause to define the column names. For example:

    WITH V( Cities )
    AS ( SELECT City
         FROM Contacts
         UNION
            SELECT City
             FROM Customers )
    SELECT * FROM V;

  • Ordering the results   You can use the WITH clause of the SELECT statement to order the column names in the select list. For example:
    WITH V( CityName )
    AS ( SELECT City AS Cities
         FROM Contacts
         UNION
            SELECT City
               FROM Customers )
    SELECT * FROM V
       ORDER BY CityName;

    Alternatively, you can use a single ORDER BY clause at the end of the list of queries, but you must use integers rather than column names, as in the following example:

    SELECT City AS Cities
       FROM Contacts
       UNION
          SELECT City
            FROM Customers
       ORDER BY 1;