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

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;