SQL Server can be pretty smart when it comes to avoiding work it’s not required to do. One instance of this is the concept of join elimination. Join elimination occurs when queries involving joins may only need to check one table instead of two (or more) based on foreign key constraints.
Let’s take a look at join elimination in action.
Read on for one example of join elimination and one more reason why those foreign key constraints are so useful.