I've been searching everywhere but I have not been able to find anything about it, seems like everyone is concerned only with WHERE and CASE. (and I haven't a SQL Server at hand reach to verify it by myself)
So, simple question: is SQL Server short circuiting JOIN based on the JOIN condition? If I write:
TableA LEFT OUTER JOIN TableB ON 1=0 AND TableA.ID = TableB.A_ID
will SQL Server skip the join at all?
Ok, someone correctly pointed out that this is solved at compile time, so I'd better expand on the question.
The above was just intended as an example, but it turned out to be a bad one. What I wanted to know is if SQL Server shorts circuit a JOIN when that condition happens at runtime, as a conseguence of a @parameter passed to a stored procedure.
I'm asking 'cause I'm sure to have used something like that back in the years, but I wanted to be 100% sure before talking about it with some colleagues. So, will SQL Server short circuit this, then:
TableA LEFT OUTER JOIN TableB ON @parameter IS NOT NULL AND TableA.ID = TableB.A_ID
if it's inside an SP and it's called with @parameter set to NULL? Will it make any difference if the store procedure is WITH RECOMPILE?