iAnywhere.com - Rules for set operations

Rules for set operations

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

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:
```
-- This is an example of an invalid statement
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 contact
UNION
SELECT city
FROM customer
UNION
SELECT city
FROM employee
```
For UNION operations, 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 contact
UNION
SELECT city
FROM customer
```
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 contact
UNION
SELECT city AS Cities
FROM customer
```
Alternatively, you can use the WITH clause to define the column names. For example:
```
WITH V( Cities )
AS ( SELECT city
     FROM contact
     UNION
     SELECT city
     FROM customer )
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 Cities = city
     FROM contact
     UNION
     SELECT city
     FROM customer )
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 Cities = city
FROM contact
UNION
SELECT city
FROM customer
ORDER BY 1
```