Does Lookup Transformation perform Left Outer Join or Not in SSIS Package - SQL Server Integration Services( SSIS) Tutorial

Scenario:

We often hear from developers that we can perform Left Outer Join by using Lookup Transformation instead of using Merge Join.
In this video we will learn if that is possible or not?

Items we will learn in this video
How to Create Two tables with some Sample Data to perform Left Join in TSQL
How to use two tables to perform Left Join by using Merge Join Transformation
How to use Lookup Ignore Failure feature to ignore errors if source record does not match with lookup reference data.
Analyize the output of TSQL Code output, Merge Join output and Lookup Output to see if all three produce same results?

Sample Tables with Sample Data

--drop table [dbo].[Lkp_Customer]
CREATE TABLE [dbo].[Lkp_Customer](
    [CustomerId] [int] NULL,
    [CustomerName] [varchar](100) NULL,
    [StreetAddress] [varchar](100) NULL,
    [City] [varchar](100) NULL,
    [State] [char](2) NULL,
    [Region] [varchar](2) NOT NULL
) 

insert into  [dbo].[Lkp_Customer]

SELECT *
FROM (
    SELECT N'1' AS [CustomerId]
        ,N'Aamir' AS [CustomerName]
        ,N'1234 Street' AS [StreetAddress]
        ,N'Sahiwal' AS [City]
        ,'PJ' AS [State]
        ,N'AS' AS [Region]
    
    UNION ALL
    
    SELECT N'2' AS [CustomerId]
        ,N'M Raza' AS [CustomerName]
        ,N'Test Street Address' AS [StreetAddress]
        ,N'Lahore' AS [City]
        ,N'PJ' AS [State]
        ,N'AS' AS [Region]
    
    UNION ALL
    
    SELECT N'3' AS [CustomerId]
        ,N'Robert' AS [CustomerName]
        ,N'1234 Street' AS [StreetAddress]
        ,N'FrankFurt' AS [City]
        ,'NA' AS [State]
        ,N'EU' AS [Region]
    
    UNION ALL
    
    SELECT N'4' AS [CustomerId]
        ,N'John' AS [CustomerName]
        ,N'1234 Street' AS [StreetAddress]
        ,N'Paris' AS [City]
        ,N'NA' AS [State] 
        ,N'EU' AS [Region]
    
    UNION ALL
    
    SELECT N'5' AS [CustomerId]
        ,N'Sam' AS [CustomerName]
        ,N'1234 Street' AS [StreetAddress]
        ,N'Charlotte' AS [City]
        ,N'NC' AS [State]
        ,N'NA' AS [Region]
    ) t;

--Create New Sample Tables

Select * into dbo.Customer1 from dbo.lkp_customer
Select top 4 * into dbo.Customer2 from dbo.lkp_customer

Select * from dbo.Customer1
Select * from dbo.Customer2

--Write Left Join TSQL Query
Select L.CustomerId,L.CustomerName,L.Region AS LeftRegion,R.Region AS RightRegion
from dbo.Customer1 AS L
Left outer join 
dbo.Customer2 AS R
on L.Customerid=R.Customerid

Can Lookup Transformation perform Left Outer Join operation in SSIS Package - SSIS Tutorial

  Related Posts / Videos on Lookup Transformation 

• How To Use Dynamic Query in Lookup Transformation in SSIS Package
• Use Stored Procedure with Parameters in Lookup Transformation ( Full Cache Mode) in SSIS Package 
• How to Force Lookup Transformation to Work as Case In-Sensitive in SSIS Package
• How Null values will be matched in Lookup Transformation in Diff Modes(Full Cache,Partial or No Cache)
• How to Handle "Row yielded no match during lookup". Error in SSIS Package
• How Lookup Transformation works for Duplicate Records in Reference Data Set
• How to Load Slowly Changing Dimension Type 1 by using Lookup Transformation ( UpSert Operation)
• Detailed Demo on Lookup Transformation Modes ( Full Cache, Partial, No Cache) by using SQL Server Profiler
• Diff between Inner join ( Merge Join Transformation) and Lookup Transformation ( Matched Output)