patternsqlMinor
Short circuit in JOIN
Viewed 0 times
circuitjoinshort
Problem
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:
will SQL Server skip the join at all?
Edit
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:
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?
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_IDwill SQL Server skip the join at all?
Edit
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_IDif 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?
Solution
DB<>fiddle and other online-services allow you to test different DBMS without having to install them locally. As shown in:
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=7692d5abba854bdc57d90c95335014c0
There is no access to table B.
You may find the following article by Lukas Eder of interest, he compares algebraic optimizations for different DBMS:
https://blog.jooq.org/2017/09/28/10-cool-sql-optimisations-that-do-not-depend-on-the-cost-model/
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=7692d5abba854bdc57d90c95335014c0
There is no access to table B.
You may find the following article by Lukas Eder of interest, he compares algebraic optimizations for different DBMS:
https://blog.jooq.org/2017/09/28/10-cool-sql-optimisations-that-do-not-depend-on-the-cost-model/
Context
StackExchange Database Administrators Q#207957, answer score: 6
Revisions (0)
No revisions yet.