CODEWITHSUNDEEP
sqlsql-server
Akshay J
Akshay J

Reputation: 5468

IN Operator SQL

I have a table called NUMS with a single column n.
And I fill values 1,2,3,4,5,null in it.

Now a query 

SELECT n FROM Nums 
 WHERE n IN (1, 2, null)

In this case I guess it's converted to 

SELECT n FROM Nums 
 Where n = 1 OR n = 2 OR n = null

I am also comparing n with a null value which should yield unknown and it should return an empty set.But it's returning 1,2 (null is not returning, although included in IN operator)

Now a query 

SELECT n FROM Nums WHERE n NOT IN(1, 2, null)

...gets converted to:

SELECT n FROM Nums 
 Where n!=1 AND n!=2 AND n!=null

Here what I said above works and it does not return anything.

Can anyone explain in detail what's happening.

Upvotes: 3

Views: 7718

Answers (7)

John G
John G

Reputation: 2156

This is because null = null is always false the operator to use for null is IS or IS NOT You can use the query below for the expected output

SELECT n FROM Nums WHERE n IN (1,2) OR n IS NULL

[Edit]Thanx @Buckwad

Upvotes: 5

Akshay J
Akshay J

Reputation: 5468

OK I have found the answer 

SELECT n FROM Nums    
WHERE n NOT IN (1, 2, null)

evaluates to 

SELECT n FROM Nums  
n!=1 AND n!=2 AND n!=null

The outcome of last comparison will always be UNKNOWN.
and the truth table of AND shows that as soon as one Unknown is invloved in it (U,T)(U,F),(U,U) the reult can only be U or F (U=Unknown, F=False) and hence it will not be included in the result set.

In case of 

SELECT n FROM Nums
WHERE n IN (1, 2, null)

equates to

SELECT n FROM Nums
WHERE n = 1 OR n =2 OR n=null

Now for the row with n=1, the operation n=1 will come as true
and for the row with n=2, the operation n=2 will come as true
and for all rows n=null will be unknown

So it gives 1 and 2 in the result set.
Hope u people liked it.
CAN ANYONE PLEASE MARK MY REPLY AS ANSWER

Upvotes: 2

KMB
KMB

Reputation: 162

 SELECT n FROM Nums  
 WHERE n IN (1, 2)
  Or n Is null

This will retrieve what you intend to get. As said by Mitch normally a comparison to Null yields UNKNOWN. This is because NULL itself is defined as an undefined value. It's like saying I lived in Nottingham, Birmingham and somewhere. Finding somewhere on the world map can prove a bit tricky as it is undefined.

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300769

If an IN list contains a NULL value anywhere, the result of the entire list is UNKNOWN

With the correct default of ANSI NULLS, when you compare NULL with NULL the result is not true, but UNKNOWN

Upvotes: 0

Aaron
Aaron

Reputation: 7108

You cannot compare directly with null. To find whether the value of a column is null, you must use something along the lines of this:

SELECT n
FROM Nums
WHERE n IS NULL

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332731

null is not returning, although included in IN operator

Because of the n = NULL evaluation; NULL can't equal NULL. It would need to be handled as n IS NULL (or similar appropriate syntax) to return the NULL row/record.

Upvotes: 1

zerkms
zerkms

Reputation: 255115

I am also comparing n with a null value which should yield unknown and it should return an empty set.

it shouldn't if you need in additional row filled with NULL values the best way imo is:

SELECT n FROM Nums WHERE n IN(1,2)
UNION
SELECT NULL

Now a query

SELECT n FROM Nums WHERE n NOT IN(1, 2, null)

...gets converted to:

Here what I said above works and it does not return anything.

thats right, because nothing can be equal to NULL and for NULL comparison IS NULL statement is used

Upvotes: 0

Related Questions