Short and Lousy
This is one of the most frustrating things I’ve seen from the optimizer in quite a while.
Here are a couple tables, with a foreign key between them:
CREATE TABLE dbo.ct(id int PRIMARY KEY, dt datetime); CREATE TABLE dbo.ct_fk(id int PRIMARY KEY, dt datetime); ALTER TABLE dbo.ct ADD CONSTRAINT ct_c_fk FOREIGN KEY (id) REFERENCES dbo.ct_fk(id);
When we use the EXISTS clause, join elimination occurs normally:
SELECT COUNT_BIG(*) AS [👍] FROM dbo.ct AS c WHERE EXISTS ( SELECT 1/0 FROM dbo.ct_fk AS cf WHERE cf.id = c.id );

But when we use NOT EXISTS, it… doesn’t.
SELECT COUNT_BIG(*) AS [👎] FROM dbo.ct AS c WHERE NOT EXISTS ( SELECT 1/0 FROM dbo.ct_fk AS cf WHERE cf.id = c.id );

Thanks for reading!
One thought on “Foreign Keys Falling Short”