Why are NULL values not selected?

26

When doing a select, I noticed that data with NULL field is not retrieved using the <> operator.

  • Why does this happen?

  • NULL is equal to char N ?

Notice that in the query below only the first row is returned.

SQLFIDDLE

    
asked by Marconi 17.09.2015 в 19:55
source

4 answers

27

Null is not a concrete string, it is null. So the SQL will not return value because you asked:

  

Returns all records that contain DIFFERENT N in my table

So SQL SERVER will return records that contain true values in the text field that are different from N, such as: true , false , 0 , -1 , or string / p>

To get around this you can use the expressions:

select * from Exemplo where texto <> 'N' OR texto IS NULL

Or you can convert null to a value

select * from Exemplo where isnull(texto, -1) <> 'N'
    
answered by 17.09.2015 / 20:02
source
21

Null is not a value. As stated in the @rray comment , null is absent of value.

Your select is bringing all fields that are not null and that are different from N .

To include the nulls in your query you need to make this explicit:

SELECT * FROM TABELA WHERE TEXTO <> 'N' OR TEXTO IS NULL

To check if a field is null in SQL you need to do

SELECT * FROM TABELA WHERE CAMPO IS NULL

The same goes for checking if a field has any value, however you use the negation operator NOT

SELECT * FROM TABELA WHERE CAMPO IS NOT NULL
    
answered by 17.09.2015 в 20:01
21

NULL is NULL , it does not compare (under normal conditions) with other things. Your selection is taking all non-null data that is different from N .

To include the nulls the query should say this explicitly.

select * from Exemplo where texto <> 'N' or texto is null

See running SQLFiddle .

In this case you are requesting all data that is different from N including unknown values

To use the correct terminology, null is the unknown value . It does not mean it has no value. It has yes, the value is null, only that it is treated in a special way. The confusion generated is one of the reasons some programmers consider null as bad.

    
answered by 17.09.2015 в 20:01
12

null is missing value, must use IS NULL to know whether or not the column has value. They are 3 values, something, empty and no value (null).

    
answered by 17.09.2015 в 20:04