LEFT OUTER JOIN

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

The below example lists all the items  (from Table A) with their company names (from Table B) irrespective whether there is any company or not. The items - Nuttella and Cup-cakes - from Table A don’t have any company details in Table B. Hence the companies are displayed NULL in the result. 

There are no Items defined for the Company IDs 21 and 22 in Table A. Hence there are no entries in the result.

One to One Use case:

Requirement: Merge employee wise daily sales data with daily plan data to 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’s ’ target on 12/09/2020 is not defined in Table B, hence the target is NULL in the result. Also, there are no sales entries for the employee ‘Boddy Jones’ on 19/08/2020 in Table A. So they are not included in the result.

Many to one Use case:

Requirement: Merge employee wise daily sales data with employee wise monthly plan data and create employee wise plan vs. actual data.

Table – A : Contains employees wise daily sales 

Table – B : Contains employees wise monthly target

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

One to Many Use case:

Requirement: Merge employee wise Monthly plan data with employee wise daily sales data to create employee wise plan vs. Actual data.

Table – A : Contains employee wise Monthly Target

Table – B : Contains employee wise daily sales

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 B, hence the sales is NULL in the result. Also the target for ‘Jason Mehta’ for the month of Sep is not defined in Table A. So they are not included in the result.

Many to Many Use case:

Requirement : Get customer wise product sales and vice versa  from the sales transaction table, 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.

Since P4 from Table A and C3 from Table B are not associated in Table C, they are not included in the result.

Join Join condition joins left outer join blend