Both JOIN and UNION are used to combine the data from one or more tables. In case of JOIN, the additional data appears in column while in case of UNION additional data appears in rows.
Suppose we have two DB2 tables, ORDERS and TRANSACTIONS. We have to extract TRANSACTION_ID for each ORDER_ID, then we will use INNER JOIN as below:
SELECT ORDER_ID, TRANSACTION_ID FROM ORDERS INNER JOIN TRANSACTIONS ON ORDERS.TRANSACTION_ID = TRANSACTIONS.TRANSACTION_ID
This query will result in 2 columns. One column will be from ORDERS table i.e., ORDER_ID and other column will be from TRANSACTIONS table i.e. TRANSACTION_ID.
We have 2 tables ORDERS and ORDER_HIST. The ORDERS table has all the current orders while ORDER_HIST table has all the archived orders. If we want to list down all the orders having total value more than 10000, then we have to use the below query.
SELECT ORDER_ID, ORDER_TOTAL FROM ORDERS WHERE ORDER_TOTAL > 10000 UNION SELECT ORDER_ID, ORDER_TOTAL FROM ORDERS_HIST WHERE ORDER_TOTAL > 10000