HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Short circuit in JOIN

Submitted by: @import:stackexchange-dba··
0
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:

TableA LEFT OUTER JOIN TableB ON 1=0 AND TableA.ID = TableB.A_ID


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:

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?

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/

Context

StackExchange Database Administrators Q#207957, answer score: 6

Revisions (0)

No revisions yet.