Execution Plan is different when Constant Variable is Null

Question

I am working on a scenario in which I am providing a variable a value. When I pass Null value, the Query Engine didn't scan the Join tables. As per Logical Query Processing, first FROM Clause Executes, then ON and JOIN executes. But in this Case the Query Engine direct go to Where Clause. Could anyone please explain the Behavior Query Engine when there is a NULL value of a Variable. I am using SQL Server 2016.

When the Value is changed then

Answer 1

You're seeing an example of contradiction detection. SQL Server's query optimizer is smart enough to perform a few quick checks at the outset of query processing to determine if it can take any shortcuts to return results without doing much (any?!) work. One such short circuit is to check if the where clause contains an "impossible" limit, such as:

WHERE 1=0

or

WHERE NULL = NULL

SQL Server sees you are comparing something to NULL, which always returns false, and simply returns an empty resultset. If the items in your WHERE clause were separated with OR instead of AND, then further checks would be performed and you might not see the constant-scan plan.

Comparing any value to NULL using = always returns false. You may want to use WHERE @c IS NULL instead. If you rewrite your query using that syntax, you'll see SQL Server does in fact "run" the query. Consider this:

IF OBJECT_ID(N'tempdb..#t', N'U') IS NOT NULL DROP TABLE #t;
CREATE TABLE #t 
(
    i int NOT NULL
);

DECLARE @c int = 1;

The query plan for this query:

SELECT *
FROM #t t
WHERE t.i = 1
    AND @c = NULL;

Versus the query plan for this query:

SELECT *
FROM #t t
WHERE t.i = 1
    AND @c IS NULL;

The problems around comparing NULL values applies to JOIN conditions as well. Consider this:

IF OBJECT_ID(N'tempdb..#t', N'U') IS NOT NULL DROP TABLE #t;
CREATE TABLE #t 
(
    i int NULL
);

IF OBJECT_ID(N'tempdb..#s', N'U') IS NOT NULL DROP TABLE #s;
CREATE TABLE #s 
(
    i int NULL
);

I'll insert a row into each table with the value of i set to NULL:

INSERT INTO #t (i) VALUES (NULL);
INSERT INTO #s (i) VALUES (NULL);

Now, if we try to JOIN the two tables in a "simple" query, we get no results returned, since NULL cannot be compared to anything, not even another NULL value!

SELECT *
FROM #t t
    INNER JOIN #s s ON t.i = s.i;

Both tables are scanned by the query processor, as shown in the query plan:

In other words, be careful using NULL values.