What are the limitations of using OUTER JOIN on a DB2 table?

DB2DatabaseBig Data Analytics

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.

raja
Published on 30-Nov-2020 13:56:04
Advertisements