I know that when a look up is performed in ADF, it's essentially a left outer join. Why use look up activity when we can use a join activity instead? What's the basic difference between them? I've looked everywhere but haven't found a solution that helps me understand this difference. Thanks!
The lookup transformation has several features built into it that are specifically there to support lookup of reference data. If you look at the configuration section of the Lookup doc (https://learn.microsoft.com/en-us/azure/data-factory/data-flow-lookup#configuration) you will see features like matching options and tagging matched rows. Lookup also support caching your lookup results (https://learn.microsoft.com/en-us/azure/data-factory/data-flow-lookup#cached-lookup).