IBM PureData System for Analytics, Version 7.1

UNION operation

The UNION operation combines the results of two subqueries into a single result that comprises the rows that are returned by both queries.

This operation differs from a join, which combines columns from two tables. A UNION expression (optional keyword DISTINCT) removes duplicate rows from the result; a UNION ALL expression does not remove duplicates.

UNION

In a UNION [DISTINCT] operation, if a tuple t appears m (>= 0) times in the first input table, and the same tuple t appears n (>= 0) times in the second input table, then that tuple t appears only once in the output table if (m + n) > 0.

{0,1,2,2,2,2,3,N,N} UNION {1,2,2,3,5,5,N,N,N}
Ë{0,1,2,3,5,N}

UNION ALL

In UNION ALL operation, if a tuple t appears m (>= 0) times in the first input table, and the same tuple t appears n (>= 0) times in the second input table, then that tuple t appears (m + n) times in the output table.

{0,1,2,2,2,2,3,N,N} UNION ALL {1,2,2,3,5,5,N,N,N}
Ë{0,1,1,2,2,2,2,2,2,3,3,5,5,N,N,N,N,N}