The result of OUTER JOIN includes matched and unmatched rows in the WHERE clause. There are two main limitations of OUTER JOINS in DB2.
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.
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.