Does SQL Server even look at a table when joining on a variable that returns false?

Question

If I join table A to table B like this...

select A.* from A 
left outer join B on A.Id = B.aId and @param = 'someValue'

and @param does not equal 'someValue', does SQL Server even attempt to match records from table B or is it smart enough to know the condition will never be true?

Answer 1

• As a general rule, SQL does not guarantee runtime boolean operator short-circuit.
• The query plan would have to work for any value of @param because plans are reused between executions

So while in a particular context you may find that the when @param has a different value then the outer join table may never be probed, you should not rely on it for correctness. Note that probe means that actual values are searched for in the table. The metadata information will always be checked. For example you cannot cheat and ask for a join to a table that doesn't exists.

In particular, do not attempt to create a single query where there should be two different ones (one that joins, one that doesn't).