Can I optimize a left join for a single match? Consider a join A LEFT JOIN B ON A.id = B.a_id:


B.a_id is nullable -- most but not all B records point to an A record.
A can, at most, only have one matching B record (business logic).
Not all A records have a match in B.


Is there a way to tell MSSQL that there will never be more than one A:B match, thus allowing it to shortcut the join once a match is found for a given A record?

I would think that such an optimization could happen if B.a_id had a unique index, but that's not possible since it's nullable.

Complicating the issue: A and B are in different databases (same MSSQL instance).

If it matters, I'm using SQL Server 2008 R2.
Wiki answer from a comment on the question by @ypercube:



You can use a filtered index:

CREATE UNIQUE INDEX a_id_uq 
ON b (a_id) 
WHERE a_is IS NOT NULL;


See Filtered Index Design Guidelines