17
In the SQL Server, by fetching all records with a given field with value NULL, if I do so no record is returned:
SELECT *
FROM clientes cli
WHERE cli.cpf = NULL
But if you use the following syntax:
SELECT *
FROM clientes cli
WHERE cli.cpf IS NULL
Correct records are returned. Why? And what’s the difference between = NULL and IS NULL?
19
When you make the comparison cli.cpf = NULL the = is used to compare values, and NULL is not a value. NULL is a space reserved to indicate the absence of value, that is why in this case one should use the predicates IS NULL or IS NOT NULL.
only to complement, in the case of update, there yes, column = null, ex: update table set column = null, so that this way the field is null, not forgetting the table settings, of course.
@Groot does not confuse comparison with association. Update tabela set campo2=null where campo1 is null. PA refers to comparison and non-association.
11
The comparison
WHERE cli.cpf = NULL
is valid as long as ANSI_NULLS is set to OFF.
As stated in the documentation of ANSI_NULLS, When SET ANSI_NULLS is OFF, the comparison operators Equal to (=) and Different from (<>) do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows with null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows with nonnull values in the column. In addition, a SELECT statement that uses WHERE column_name <> Xyz_value returns all lines that are not Xyz_value and that are not null.
-- código #1
set ANSI_NULLS ON;
SELECT colunas
FROM clientes cli
WHERE cli.cpf = NULL;
SELECT colunas
FROM clientes cli
WHERE cli.cpf is NULL;
and
-- código #2
set ANSI_NULLS OFF;
SELECT colunas
FROM clientes cli
WHERE cli.cpf = NULL;
SELECT colunas
FROM clientes cli
WHERE cli.cpf is NULL;
However, always use the IS NULL form and keep ANSI_NULLS as ON.
Documentation:
According to the documentation ANSI_NULLS will always be ON after some future version... However, good response
@Sorack: this remark has been there (at least) since the 2005 version of SQL Server! rs // See https://msdn.microsoft.com/pt-br/library/ms188048(v=sql.90). aspx //
7
The NULL is the undefined, undetermined and indefinable. It is not false, but neither is it true. Neither 8 nor 80. It is not a value, but the lack of one. Because it is not a value, it can be considered a state. A state of indefinite.
By having this definition, by comparing NULL using the standard comparators (equal, different from, greater than, less than, greater than, equal to, less than) will always result in false.
SELECT NULL = 0 // => false
SELECT NULL != 0 // => false
SELECT NULL = NULL // => false
Anything that compares to NULL, is false. This explains why NULL is the absence of value, the undefined and the lack of a value.
To better understand, I like to say that evalua to false why it is not possible to affirm that it is true.
Think of a hypothetical table in a database, which holds records of people:
+----+-------+-------+
| ID | Nome | Idade |
+----+-------+-------+
| 1 | João | NULL |
| 2 | Maria | 19 |
| 3 | José | NULL |
+----+-------+-------+
Then I ask the following questions to the database:
How old is Maria? 19.
How old is João? I don’t know.
Mary and John are the same age? I can’t say that. Neither false nor true.
The same happens (3) with the question "John and Joseph are the same age?".
That is why comparisons with NULL need special treatment. And then came the IS NULL.
Great example +1
The introduction was worthy of the Master Programmer, the answer of Ono Sendai
Browser other questions tagged sql database sql-server query select
You are not signed in. Login or sign up in order to post.
Null is absence of information , nothing can be equal to null not even null , the correct syntax is IS NULL , if A is null and B is null A=B returns false for example.
– Motta
related https://answall.com/questions/87514/null-%C3%A9-equals-a-n
– user60252
The result depends on the configuration of ANSI_NULLS
– José Diz