3

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

7

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.


In future, please don't post pictures of code, instead create a minimal, complete, and verifiable example that others can use in their answer.

  • 1
    Thank You Max. Really appreciate your effort in explaining the above logic. I will be very careful in future about the code in pictures. Thanks Once again. – Ashraf Jan 17 at 6:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.