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.

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.

Mandalika

Published on 30-Nov-2020 09:26:04

• Related Questions & Answers
• Usage and syntax of INNER and OUTER JOIN in DB2
• What are the limitations of using MySQL views?
• What are the limitations of JavaScript?
• What are the limitations of Selenium?
• What are the limitations of MySQL ENUMs?
• What are the limitations of Selenium RC?
• INNER JOIN vs FULL OUTER JOIN vs LEFT JOIN vs RIGHT JOIN?
• What are the advantages and limitations of JDBC PreparedStatement?
• What are the limitations of array in C language?
• Difference Between Inner Join and Outer Join in SQL
• INNER JOIN vs FULL OUTER JOIN vs LEFT JOIN vs RIGHT JOIN in PostgreSQL?
• What are importance and limitations of financial ratios?
• How to get the list of all COBOL-DB2 programs using a DB2 table TAB1?
• Python - Merge Pandas DataFrame with Outer Join
• How to perform a left outer join using linq extension methods in C#?