Can I optimize a left join for a single match?
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