Difference between UNION and UNION ALL in DB2

The UNION in DB2 is used to merge two or more SELECT statements. The SELECT statements can be on a single table or on multiple tables. Each SELECT statement is processed individually and their results are combined to give us the final result rows.

The UNION statement will eliminate the duplicate rows appearing as a result of SELECT statements. If we want to retain the duplicate rows, then we can use the UNION ALL statement.

For example, if we want to extract all ORDER_ID with ORDER_TOTAL greater than 1000 in ORDERS and ORDERS_HIST table, we can use the below query with a UNION statement.

Example

SELECT ORDER_ID FROM ORDERS WHERE ORDER_TOTAL > 1000
UNION
SELECT ORDER_ID FROM ORDERS_HIST WHERE ORDER_TOTAL > 1000