The Merge analysis merges together tables or views by performing an SQL UNION, INTERSECT or MINUS operation. The merge operation brings together rows from two or more tables, matching up the selected columns in the order they are selected. This can be contrasted with the Join function that brings together columns from multiple tables. The rows contained in the answer set are determined by the choice of the Merge Style, determining whether the Union, Intersect or Minus operator is applied to each table after the first table selected. An additional option is provided to determine if duplicate rows, if any, should be included in the answer set. You may 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. The final answer table contains all the qualifying rows from each table. With the Union merge style, 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.
When the Intersect merge style is selected, 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 answer 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, it is not included in the answer set.
When the Minus merge style is selected, the rows containing selected columns from the first table are included in the answer table provided they do not appear in any of the other selected tables. This is achieved using the SQL MINUS operator for each table after the first. The MINUS operator is a Teradata specific SQL operator equivalent to the standard EXCEPT operator.