Usage and syntax of INNER and OUTER JOIN in DB2

Problem: How to explain INNER JOIN and OUTER JOIN with the help of an example on ORDERS and TRANSACTION DB2 table.

Solution

The JOIN is used to combine data from one or more tables in DB2. There are two main types of JOIN — INNER JOIN and OUTER JOIN. The basic difference between them is, INNER JOIN is an intersection of two or more tables while outer join is union of two or more tables. Basically, INNER JOIN is used to combine the data from multiple tables using equal column value and on the other hand, in case of OUTER JOIN, if the column values are not equal then also the row will b   e displayed with NULL values.

For example, consider the table below.

ORDERS

TRANSACTIONS

For INNER JOIN, we will use the below query.

Example

SELECT ORDER_ID, TRANSACTION_ID FROM
   ORDERS FULL OUTER JOIN TRANSACTIONS ON
   ORDERS.TRANSACTION_ID = TRANSACTIONS.TRANSACTION_ID

For OUTER JOIN, we will use the below query.

Example

SELECT ORDER_ID, TRANSACTION_ID FROM
   ORDERS FULL OUTER JOIN TRANSACTIONS ON
   ORDERS.TRANSACTION_ID = TRANSACTIONS.TRANSACTION_ID