In this post and video at SQLPerformance.com, I discuss what join elimination is and how it works in SQL Server. Join elimination is one of the many techniques that the SQL Server query optimizer uses to create efficient query plans. Specifically, join elimination occurs when SQL Server can establish equality by using query logic or trusted database constraints to eliminate unnecessary joins... Read more or watch the video