Sql-server – Execution Plan is different when Constant Variable is Null

execution-planoptimizationperformancequery-performancesql serversql-server-2016

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.

enter image description here

When the Value is changed then

enter image description here

Best Answer

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;

enter image description here

Versus the query plan for this query:

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

enter image description here

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;

enter image description here

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

enter image description here

In other words, be careful using NULL values.

Related Question