FULL JOIN

FULL   JOIN returns matched and unmatched rows from both tables (it's a union of both). If there is no match, the missing values will be inserted as NULL.

The following example has all the Item details from table A and all the company data from Table B. Since there are no companies associated with Items 1,2 and 3 (from Table A) and there are no items under the companies 21 and 22 (from Table B), their values are displayed as NULL 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, the employee ‘Boddy Jones’s’ target on 12/09/2020 is not defined in Table B, and his sales is not defined in Table A for 19/08/2020. Hence, the target and sales values are NULL in the resultset for these dates.

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

In the below example, the employee Jason Mehta’s target is not defined for the month of Sep in Table B. Similarly, the employees ‘David Brown’ (for Sep) and Jason Mehta (for Aug) don’t have any sales entries in Table A. Hence, the values are NULL 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, the employees ‘David Brown’ (for Sep) and Jason Mehta (for Aug) don’t have any sales entries in Table B. Similarly, the employee Jason Mehta’s target is not defined for the month of Sep in Table A. So the result set contains NULL values for these entries.

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 : Transaction Sales Table which contains Product ID, Customer ID and Sales.

As per the below example, for Full JOIN, the Product ID P4 from Table A, Customer ID C3 from Table B, and the sales entry for P5 – C4 from Table C will also be included in the result set.

blend Join Join condition joins Full join