Conor vs. FOREIGN KEY join elimination

I received a question from a friend in Brazil related to foreign keys and join elimination in SQL Server.  Yes, SQL Server can detect that some joins are not needed to return results to the user and skip them.  What is this evil magic, you ask?  Well, let’s walk through it before we get out…

4