Have you ever encountered a situation where two similar queries return different results? If so, you’re not alone. In this blog post, we will explore a puzzle related to SQL Server query results and understand why different queries can produce different outcomes.

Let’s start by examining two queries:

SET ANSI_NULLS ON;

-- Query 1
SELECT 'SQLAuthority' AS Statement11
WHERE 'Authority' IN ('S', 'Q', 'L', 'Authority', NULL);

-- Query 2
SELECT 'SQLAuthority' AS Statement12
WHERE 'Authority' NOT IN ('S', 'Q', 'L', NULL);

If you run these queries, you will notice that Query 1 returns a result, while Query 2 does not. But why is that?

The answer lies in the behavior of the IN and NOT IN operators. Let’s break it down:

Query 1

In Query 1, we use the IN operator to check if the value ‘Authority’ exists in the given list (‘S’, ‘Q’, ‘L’, ‘Authority’, NULL). The IN operator is equivalent to multiple OR conditions.

So, the query can be rewritten as:

SELECT 'SQLAuthority' AS Statement11
WHERE 'Authority' = 'S'
   OR 'Authority' = 'Q'
   OR 'Authority' = 'L'
   OR 'Authority' = 'Authority'
   OR 'Authority' = NULL;

When evaluating this query, each condition is checked individually. In this case, the condition ‘Authority’ = ‘Authority’ evaluates to TRUE. Since any TRUE condition will result in a match, the query returns the value ‘SQLAuthority’.

Query 2

In Query 2, we use the NOT IN operator to check if the value ‘Authority’ does not exist in the given list (‘S’, ‘Q’, ‘L’, NULL). The NOT IN operator is equivalent to multiple AND conditions with negation.

So, the query can be rewritten as:

SELECT 'SQLAuthority' AS Statement12
WHERE 'Authority' != 'S'
   AND 'Authority' != 'Q'
   AND 'Authority' != 'L'
   AND 'Authority' != NULL;

When evaluating this query, each condition is checked individually. In this case, all conditions evaluate to TRUE, except for the last one (‘Authority’ != NULL), which evaluates to FALSE. Since all conditions must be TRUE for a match, the query does not return any result.

It’s important to note that the behavior of these operators can be influenced by the ANSI_NULLS setting. When ANSI_NULLS is ON, any comparison operation involving NULL will evaluate to UNKNOWN. For a record to be included in the result, all conditions for that record must evaluate to TRUE.

Keep in mind that different results can be obtained if the ANSI_NULLS setting is OFF. This is something to consider when working with SQL Server queries.

I hope this blog post has shed some light on the puzzle and helped you understand why Query 1 returns results while Query 2 does not. Stay tuned for more discussions on SQL Server concepts in future blog posts.

Thank you for reading!