When doing a select, I noticed that data with
NULL field is not retrieved using the
Why does this happen?
NULLis equal to
Notice that in the query below only the first row is returned.
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:
-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'
Null is not a value. As stated in the @rray comment ,
null is absent of value.
select is bringing all fields that are not null and that are different from
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
SELECT * FROM TABELA WHERE CAMPO IS NOT NULL
NULL , it does not compare (under normal conditions) with other things. Your selection is taking all non-null data that is different from
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.
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).