Usage and syntax of INNER and OUTER JOIN in DB2

DB2DatabaseBig Data Analytics

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

ORDER_ID
ORDER_DATE
TRANSACTION_ID
A11243
10-08-2020
Z67899
A22178
11-08-2020
Z67009

 

TRANSACTIONS

TRANSACTION_ID
TRANSACTION_DATE
Z67899
11-08-2020
Z67674
07-08-2020

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
ORDER_ID
TRANSACTION_ID
A11243
Z67899

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
ORDER_ID
TRANSACTION_ID
A11243
Z67899
A22178
NULL
NULL
Z67674
raja
Published on 30-Nov-2020 08:59:52
Advertisements