Combine tables with UNION, INTERSECT, and EXCEPT

Netezza Performance Server supports three classes of set operations:

• UNION [DISTINCT] and UNION ALL
• INTERSECT [DISTINCT] and INTERSECT ALL
• EXCEPT [DISTINCT] or MINUS [DISTINCT] and EXCEPT ALL, MINUS ALL

SQL statements that contain set operators are called compound queries and each SELECT statement in a compound query is called a component query. You can combine two or more SELECT statements into a compound query if they satisfy the following union compatibility conditions:

• The result sets of both queries must have the same number of columns.
• The corresponding columns in the two queries must have the same data type or must be implicitly convertible to the same data type.

  For example, you can have a column in the first component query be the data type CHAR that corresponds to the VARCHAR column in the second component query or vice versa. You cannot, however, have a column in the first component that is a DATE and the corresponding column in the component is a CHAR. For more information about data type conversions, see Data type promotion.

The syntax for a set operation is:

<SELECT-statement>
{UNION | INTERSECT | EXCEPT | MINUS} [ALL | DISTINCT]
<SELECT-statement>
{UNION | INTERSECT | EXCEPT | MINUS} [ALL | DISTINCT]
<SELECT-statement>]*
[ORDER BY …]
[LIMIT …]

Set operations have the following restrictions:

• If the names of the corresponding columns match, SQL uses that column name in the result. If the corresponding column names differ, SQL uses the column name from the first query in the set statement. If you want to rename a column in the result, use an AS clause in the first query.
• You can specify an optional ORDER BY clause only in the final query in the set statement. SQL applies the sort to the final combined result.
• You can specify an optional LIMIT clause after the ORDER BY. SQL applies the limit to the final combined result.
• You can specify GROUP BY and HAVING only in individual queries. You cannot use them to affect the result.