Why are NULL values not selected?

Asked

Viewed 5,507 times

33

When making a select, I noticed that the data with field NULL are not recovered using the operator<>.

  • Why does this happen?

  • NULL is equal to a char N?

See that in the query below only the first line is returned.

SQLFIDDLE

sql database sql-server null sqlfiddle
  • 1

    null is no value, need to use, something like select * from where coluna is null

  • 1

    As @rray said, NULL is the absence of value, and <> compares with values, so does not come the line with NULL.

  • If I remember correctly the absence or presence of NULL can only be tested using "IS NOT NULL" or "IS NULL". Normal comparisons do not work. It’s more or less like the floating point Nan, which is different from any number, even from himself.

  • When should we allow a column of a table of a database to accept NULL?

  • 1

    Recommended reading: http://answall.com/questions/90363/o-que-realmente-significa-null/90378#90378

4 answers

30

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

Returns all records that contain value DIFFERENT FROM N on my table

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

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'

25


NULL is NULL, it does not compare (under normal conditions) with other things. Your selection is picking up all data nonzero that are different from N.

To include nulls the query must say this explicitly.

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

Behold working in the Sqlfiddle. Also put on the Github for future reference.

In this case you’re asking all data other than N including unknown values

To use the correct terminology, null is the unknown value. It does not mean that it has no value. 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 something bad.

24

Null is not a value. As stated in commenting of @rray, null is no value.

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

To include 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, but you use the negation operator NOT

SELECT * FROM TABELA WHERE CAMPO IS NOT NULL

15

null is no value, need to use IS NULL to know if the column has value or not. Are 3 values, something, empty and no value(null).

Browser other questions tagged sql database sql-server null sqlfiddle

You are not signed in. Login or sign up in order to post.