RIGHT JOIN

A RIGHT OUTER JOIN returns all the rows from the right table (TableB) with the matching rows from the left table (TableA) or NULL – if there is no match in the left table.

The following example will return all the companies (from Table B ) and their items (from Table A) if there are any. For the Company IDs 21 and 22 from Table B, there are no matching Items in Table A. So they are NULL in the result. Also the Item IDs 1, 2 and 3 are not associated with any Companies. So they are not displayed in the result.

One to One Use case:

Requirement : Merge daily plan data with actual daily sales data of various employees and create plan vs actual data

Table – A : Contains Daily sales of various employees

Table – B : Contains Daily Target of various employees

As per the below example, Employee ‘Boddy Jones’ from Table B doesn’t have any sales entry on 19/08/2020 in Table A, hence it is displayed as NULL in the result. Also the target is not defined for ‘Boddy Jones’ on 12/09/2020 in Table B. So the record on 12/09/2020 from Table A is not included in the result.

Many to One Use case:

Requirement: Merge Employee wise Daily Sales with Employee wise Monthly Plan Data and create Employee wise Daily Actual vs Plan Data .

Table – A : Employee wise Daily Sales

Table – B : Employee wise Monthly Target

As per the below example, there is no sales entry for the Employee ‘Jason Mehta’ in the month of Aug and ‘David Brown’ in the month of Sep in Table A, hence the sales is NULL in the result. Also the target for ‘Jason Mehta’ for the month of Sep is not defined in Table B. So they are not included in the result.

One to Many Use case:

Requirement : Merge Employee wise Monthly Target with Employee wise Daily Sales and create Employee wise Monthly 

Plan vs Actual Data.

Table – A : Employee wise Monthly Target

Table – B : Employee wise Daily Sales

In the below example, Employee ‘Jason Mehta’s’ Target is not defined for the month of Sep in Table A. Hence it is displayed NULL in the result. Also in Table B, there is no sales entry for ‘David Brown’ in the month of Sep and ‘Jason Mehta’ in the month of Aug. So these rows   from Table A are not displayed in the result.

Many to Many Use case:

Requirement  : Get customer wise products sales and vice versa from customer master and product master

Table – A : Product Master Table which contains Product Id and Product Name.

Table – B : Customer Master Table which contains Customer Id and Customer Name.

Table – C : Sales Transaction Table that has Product and Customer wise Sales.

As per the below example, Product P5 and Customer C4 are not defined in their master tables. Hence, the sales entry of P5 and C4 are not included in the result.

blend Join Join condition joins right join