Join hint to specify that a left join can return at most 1 row

Question

Let's say you are doing a count query that contains a left join. Let's also say that we know for a fact that the left join will return at most 1 row (for each outer row). From this, we can conclude that the left join cannot affect the count, hence it is unnecessary to perform the left join. In SQL Server, is it possible to express that a left join can result in at most 1 row being returned, thereby allowing the optimiser to skip the left join for a count query? I know you can do it with an outer apply, but I was wondering if you could do with with a plain left join. In my particular case, not performing this left join for the count query saves a lot of time.

Outer Apply example

Select count(*)
from table1 a
outer apply ( select top 1 * from table2 b where a.id = b.id) q

The optimiser is smart enough to not perform the outer apply. My question is, can you also do this with a plain left join?

Answer 1

Not directly, a left join will always return all matches from the joined table/view.

Less directly, you could use a CTE with the row_number window function and left join to that with a filter on rownumber=1. This may be more efficient than your outer apply depending on other parts of the query but you'll need to look at plans produced an IO stats of each option with realistic data to be sure either way.