IN SQL statement

Question

I have a table called NUMS with one column n.
And I fill in the values ​​1,2,3,4,5, zero in it.

Now request

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

In this case, I think it is converted to

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

I also compare n with a null value, which should produce the unknown, and it should return an empty set. But it returns 1.2 (null is not returned, although it is included in the IN statement)

Now request

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

... converts to:

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

Here, what I said above works and returns nothing.

Can someone explain in detail what is happening.

Answer 1

Ok i found the answer

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

estimated as

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

UNKNOWN.
, , (U, T) (U, F), (U, U), U F (U = Unknown, F = False) , , .

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

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

n = 1 n = 1 true n = 2 n = 2 true n = null

, 1 2 .
, .
, ...

Answer 2

, null = null false, null - IS IS NOT

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

[] Thanx @Buckwad

Answer 3

null , IN

- n = NULL; NULL NULL. n IS NULL ( ) / NULL.

Answer 4

. , , - :

SELECT n
FROM Nums
WHERE n IS NULL

Answer 5

n , , .

, NULL, imo:

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

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

... :

, , .

thats right, NULL NULL NULL

Answer 6

IN NULL , UNKNOWN

ANSI NULLS, NULL NULL, , UNKNOWN

Answer 7

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

This will get what you intend to get. According to Mitch, usually a comparison with Null gives UNKNOWN. This is because NULL itself is defined as undefined. I like to say that I lived in Nottingham, Birmingham and somewhere. Finding somewhere on a world map may seem a bit complicated, as it is undefined.