- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What is the difference between DB2 JOIN and UNION? Explain with the help of an example
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