NULL effects with IN and NOT IN Clause

Introduction

Treatment of NULL values is an important factor when we are writing query. Sometimes it provide vigorous output if we don't treat NULL. Here in this article I am going to explain a particular scenario with NULL effects.

Scenario

-- Query-1

SELECT 'OK' AS RESULT WHERE 10 IN(1, 2, 4, 10,NULL);

-- Query-2

SELECT 'OK' AS RESULT WHERE 10 NOT IN(1, 2, 4, NULL);

Consider the Query-1 WHERE conditions. It provides the proper output. But in Query-2 not provide any output.

Reason

If we have a closer look, the Query-1 is actually.

 SELECT 'OK' AS RESULT WHERE 10 = 1

                            OR 10 = 2

                            OR 10 = 4

                            OR 10 = 10

                            OR 10 = NULL;

In WHERE condition we find OR operators, so it works fine for 10 = 10.

But for Query-2

SELECT 'OK' AS RESULT WHERE 10 <> 1

                            AND 10 <> 2

                            AND 10 <> 4

                            AND 10 <> NULL; 

Here we find the AND operator and 10 is comparing with NULL. Here NULL is UNKNOWN.

ANSI_NULLS ON|OFF

When this is on, any query that compares a value with a null returns a 0. When off, any query that compares a value with a null returns a null value.

What MSDN says

"When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name =NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL."

By default the ANSI_NULLS is ON. Now we make it OFF and see the result set of Query-1 and Query-2.

SET ANSI_NULLS OFF

GO

-- Query-1

SELECT 'OK' AS RESULT WHERE 10 IN(1, 2, 4, 10,NULL);

GO

-- Query-2

SELECT 'OK' AS RESULT WHERE 10 NOT IN(1, 2, 4, NULL);

GO

SET ANSI_NULLS ON

GO

Here in this case both the query is returning the same output.

Finding NULL Values

To find NULL values we can use

SELECT * FROM table_name WHERE columns_name IS NULL;

But if ANSI_NULLS is OFF we can write this also

SET ANSI_NULLS OFF                           

SELECT * FROM table_name WHERE columns_name = NULL;

Or by treating NULL values we can write this

SELECT * FROM table_name

WHERE ISNULL(columns_name, '') = '';

Hope you like it.

Posted by: MR. JOYDEEP DAS