Incorrect cardinality estimation in join on trusted foreign keys
Query-1:- Join two tables and also add lookup table filed into select
Query-2:- Join two tables and does not added lookup table filed into select. Just to prove that trusted foreign key exists between two tables.
Query-3:- Selecting all the rows from base table where corrosponding rows does not exists in lookup table. This query returns 0 row, but estimate was showing 2231 rows.
Plan:- link text
Same 2231 rows difference exists for query 1.
Is there any reason why optimizer reducting the estimates in join done on columns involved in foreign key relationship?
Thanks in advance for any insight.
Despite the "Won't Fix" status of the linked Connect item, it is still possible this issue will be addressed by other work, but for the moment, that's how things stand.