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

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 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;