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).

Answer 2

It's easier to put this code in a new answer rather than a comment, but this shows a bit of what Remus is saying:

CREATE PROCEDURE dbo.Test_Params
    @param1 INT
WITH RECOMPILE
AS
BEGIN
    SELECT
     o.object_id,
     c.object_id
    FROM
     sys.objects o
    LEFT OUTER JOIN sys.columns c ON
     c.object_id = o.object_id AND
     @param1 = 1
    OPTION
     (RECOMPILE)
END
GO


EXEC dbo.Test_Params 1
EXEC dbo.Test_Params 2

If you do an execution plan on the two EXEC statements, you'll see that sys.columns appears in both execution plans, but in the second the rows are all filtered before the join takes place. Notice that they are not short-circuited out of the query entirely though.