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}