Sql-server – Short circuit in JOIN

join;sql servert-sql

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?

Best Answer

  1. 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?
  2. Will it make any difference if the store procedure is WITH RECOMPILE

Data Setup

CREATE TABLE TableA(ID INT PRIMARY KEY);
CREATE TABLE TableB(A_ID INT);

INSERT INTO TableA VALUES (1), (2), (3);

INSERT INTO TableB VALUES (1), (1), (1), (1), (1), (1), (2), (3);

Stored proc

CREATE PROC P1 @parameter INT
AS
    SELECT *
    FROM   TableA
           LEFT OUTER LOOP JOIN TableB L
             ON @parameter IS NOT NULL
                AND TableA.ID = L.A_ID
           LEFT OUTER MERGE JOIN TableB M
             ON @parameter IS NOT NULL
                AND TableA.ID = M.A_ID
           LEFT OUTER HASH JOIN TableB H
             ON @parameter IS NOT NULL
                AND TableA.ID = H.A_ID;

The plan for this after executing EXEC P1 1 looks as follows.

enter image description here

  • All of the join types are able to put a filter with a startup predicate above the leaf operator accessing table B. Meaning at runtime it is only scanned if needed.
  • The actual and estimated rows are spot on for this example.

However the estimated rows depend on which parameter value is passed when the plan is compiled. If NULL had been passed the estimated number of rows in the final operator is 1.37074. In this simple plan the fact that 218 rows can be emitted for a subtree with estimated 1.37 rows probably won't make much difference but it could do if this was part of a larger query. And even in this simple example it could mean memory grants for the final hash join are incorrect.

Logically an outer join can't reduce the number of rows but for some reason the initial 3 gets scaled down to 1.37074 after passing through the two other outer joins here.

If somewhat accurate cardinality estimates are required then WITH RECOMPILE does solve this issue - the cardinality estimates are either 1.37074 or 218 dependant on the nullability of the parameter. But OPTION (RECOMPILE) would be preferable. As well as being targeted at the specific statement it also allows additional simplifications to be made.

With OPTION (RECOMPILE) in place the execution plan when calling with NULL is as below.

enter image description here

The actual and estimated rows are spot on and all redundant operators have been removed from the plan (so it won't spend time creating build input for the unneeded hash join in this case).

Plans above generated on build 14.0.1000.169

Related Question