The Merge analysis function merges tables or views by performing an SQL UNION, INTERSECT, or EXCEPT operation. The merge function brings together rows from two or more tables, matching up the selected columns in the order the rows are selected. Results can be compared with Join function results, which brings together columns from multiple tables. The rows contained in the answer set are determined by the choice of the merge style and by determining whether the UNION, INTERSECT, or EXCEPT operator is applied to each table after the first table is selected.
An additional option is provided to determine if there are any duplicate rows to include in the answer set. You can also specify one or more optional SQL WHERE clauses to apply to selected tables (each WHERE clause is applied to just one table). When the UNION merge style is selected, the union of the rows containing selected columns from the first table and each subsequent table is performed using the SQL UNION operator.
| Merge Style | Description |
|---|---|
| UNION | An option is provided to add an identifying column to the answer set and to name the column if desired. This column assumes an integer value from 1 to n to indicate the input table each row in the answer set comes from. |
| INTERSECT | The intersection of the rows containing selected columns from the first table and each subsequent table is performed using the SQL INTERSECT operator. The final output table contains all the qualifying rows that exist in each of the tables being merged. That is, if a row is not contained in each of the requested tables, the row is not included in the answer set. |
| EXCEPT | The rows containing selected columns from the first table are included in the output table provided that the rows do not appear in any of the other selected tables. This is achieved by using the SQL EXCEPT operator for each table after the first. The EXCEPT operator is the standard equivalent of the Teradata-specific SQL MINUS operator. |
Merge Characteristics
- Each input table must have the same number of columns
- The columns in the first table determine the column names and types in the result table
- An SQL runtime error is returned if the columns in the second and subsequent input tables are incompatible with the columns in the first input table
- (-3654) Corresponding select-list expressions are incompatible
- There is no allowance for mixing set operators; all set operators must be the same (for example, two UNION operators when there are three input tables.)
- There is no allowance for the use of parentheses to determine order of operators