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 are the limitations of using OUTER JOIN on a DB2 table?
The result of OUTER JOIN includes matched and unmatched rows in the WHERE clause. There are two main limitations of OUTER JOINS in DB2.
- The WHERE clause of OUTER JOIN can only have ‘=’ relational operator. <,>, etc are not allowed in case of OUTER JOIN of two or more tables. Also two or more conditions in WHERE clause can only be used with AND logical operator, other logical operators such as OR, NOT is not allowed.
- The functions to handle NULL operators such as VALUE and COALESCE could not be used with the OUTER JOINS.
For example, if we have below 2 DB2 tables which stores ORDERS and TRANSACTION details respectively.
| ORDER_ID | TRANSACTION_ID | ORDER_DATE |
| Z22345 | ITX4489 | 23-10-2020 |
| Z62998 | ITX4311 | 21-10-2020 |
| Z56902 | ITX3120 | 26-10-2020 |
| TRANSACTION_ID | TRANS_AMT | TRANS_ORDER_DT |
| ITX4489 | 1128 | 24-10-2020 |
| ITX4311 | 2318 | 17-10-2020 |
| ITX3120 | 88956 | 26-10-2020 |
| ITX2167 | 5612 | 12-10-2020 |
The OUTER JOIN in these two tables can be done as below.
Example
SELECT A.ORDER_ID, B.TRANSACTION_ID, B.TRANS_AMT FROM ORDERS A FULL OUTER JOIN TRANSACTION B ON B.TRANS_ORDER_DT = A.ORDER_DATE
However, we cannot give > or < relational operator while comparing TRANS_ORDER_DT and ORDER_DATE on full outer join.
Updated on: 30-Nov-2020
357 Views
Advertisements