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.
Published on 30-Nov-2020 13:56:04
