cfradenburg (10/21/2010)
garima.arya (10/21/2010)
If someone has added a foreign key constraint, why would he write a query like this.Interesting point about how the query optimizer works but I'm more with Garima. If there's a foreign key constraint there what's the advantage of putting the exists check in the code? The only thing I can think of is that it will make it a little easier to follow if you're not familiar with the DB but I wouldn't think that would be worth giving the optimizer the possibility of generating an execution plan that will scan both tables.
I think that trusted foreign key constraints provide information that can be used to cut out more of an execution plan then just the simple exists used in this article. It is fact that multiple trusted references to the same target table signify that the data in those foreign key fields is directly comparable to eachother. So the query optimizer can figure this out and simplify execution plans.
As for the arguments that this only involves queries written without build in knowledge by the writer, consider views that server multiple purposes. For views it is accepted that the optimizer has to cut out unused parts based on relevance to the consuming query. The writer of the consuming query might have the knowledge, but not the means to do this for the optimizer in this instance, nor does the writer of the view.