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

DB2DatabaseBig Data Analytics

#### Oracle DB Online Training

41 Lectures 8.5 hours

#### Oracle DB 12c Online Training

33 Lectures 7 hours

#### Cosmos DB

30 Lectures 1 hours

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 09:26:31