- 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
Usage and syntax of INNER and OUTER JOIN in DB2
Problem: How to explain INNER JOIN and OUTER JOIN with the help of an example on ORDERS and TRANSACTION DB2 table.
Solution
The JOIN is used to combine data from one or more tables in DB2. There are two main types of JOIN — INNER JOIN and OUTER JOIN. The basic difference between them is, INNER JOIN is an intersection of two or more tables while outer join is union of two or more tables. Basically, INNER JOIN is used to combine the data from multiple tables using equal column value and on the other hand, in case of OUTER JOIN, if the column values are not equal then also the row will b e displayed with NULL values.
For example, consider the table below.
ORDERS
ORDER_ID | ORDER_DATE | TRANSACTION_ID |
A11243 | 10-08-2020 | Z67899 |
A22178 | 11-08-2020 | Z67009 |
TRANSACTIONS
TRANSACTION_ID | TRANSACTION_DATE |
Z67899 | 11-08-2020 |
Z67674 | 07-08-2020 |
For INNER JOIN, we will use the below query.
Example
SELECT ORDER_ID, TRANSACTION_ID FROM ORDERS FULL OUTER JOIN TRANSACTIONS ON ORDERS.TRANSACTION_ID = TRANSACTIONS.TRANSACTION_ID
ORDER_ID | TRANSACTION_ID |
A11243 | Z67899 |
For OUTER JOIN, we will use the below query.
Example
SELECT ORDER_ID, TRANSACTION_ID FROM ORDERS FULL OUTER JOIN TRANSACTIONS ON ORDERS.TRANSACTION_ID = TRANSACTIONS.TRANSACTION_ID
ORDER_ID | TRANSACTION_ID |
A11243 | Z67899 |
A22178 | NULL |
NULL | Z67674 |