Did you know that sometimes it is faster to use a Find Replace Tool instead of using a Join Tool with a Union Tool for a Left Outer Join equivalent?
For those of you who are not familiar with SQL and what a Left Outer Join is, refer to the diagram below;
Inner Join - This is when you only pull data that is in both the Left and Right table based on a column (also known as a Primary Key) that is the same in both data sets (such as an account number or customer id field). If you were performing a VLOOKUP against two spreadsheets, these are the results from your VLOOKUP and not your #N/A values.
Left Outer Join - this is when you are bringing in data from the Inner Join AND the data from the Left Table that did not have a corresponding account number or customer id in the Right Table. You are bringing the data into your data set because you need it anyway. In excel these are the rows that had the #N/A result.
Using Comic Book examples, I have two tables below. The Left Table contains Star Wars and DC Characters. The Right Table contains a couple of these superheroes with a column called Popularity. I want to bring in this popularity column to have it display next to the Franchise column in my Left Table.
First I would use the Join Tool to join these two tables on the Primary Key or the Superheroes column. I also removed the duplicate Superheroes column from the Right Table.
The J Output Anchor (Join) only combined data for Luke Skywalker, Superman and Boba Fett.
What about the other Superheroes? I would add a Union Tool and input the results from the L Output Anchor and the J Output Anchor.
This returns Null Values for the Superheroes that were not in the Right Table. You would see an #N/A for these values if you were performing a VLOOKUP in Excel.
You can return these same results with the Find Replace Tool instead of using the Join and Union Tools.
The Left Table would stream into the F input Anchor (Find) of the Find Replace Tool. The Right Table would stream into the R input Anchor (Replace).
I would select the following configuration for the Find Replace Tool;
Find
Any Part of Field
Find Within Field
Superheroes
Find Value
Superheroes
Append Field(s) to Record
Popularity
My results appear the same as the Left Outer Join Tool combination;
*Notice they are in a different order. You will need to add a Sort Tool if you want the results in a different order.