What is the difference between DB2 JOIN and UNION? Explain with the help of an example

DB2DatabaseBig Data Analytics

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.

For example,

JOIN

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:

Example

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.

UNIONS

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.

Example

SELECT ORDER_ID, ORDER_TOTAL FROM ORDERS WHERE ORDER_TOTAL > 10000
UNION
SELECT ORDER_ID, ORDER_TOTAL FROM ORDERS_HIST WHERE ORDER_TOTAL > 10000
raja
Published on 30-Nov-2020 08:57:09
Advertisements