In%%, when fetching all records with a given field with value SQL Server , if I do the following no records are returned:
SELECT *
FROM clientes cli
WHERE cli.cpf = NULL
However, if you use the following syntax:
SELECT *
FROM clientes cli
WHERE cli.cpf IS NULL
The correct records are returned. Because? And what is the difference between NULL and = NULL ?
When you make the comparison cli.cpf = NULL the = is to compare values, and NULL is not a value. NULL is a placeholder to indicate the absence of value, which is why in this case you must use the IS NULL or IS NOT NULL predicates.
The comparison
WHERE cli.cpf = NULL
is valid as long as ANSI_NULLS is set to OFF.
As stated in the ANSI_NULLS documentation, When SET ANSI_NULLS is OFF, the comparison operators equal to (=) and non (<) 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 non-null values in the column. Additionally, a SELECT statement that uses WHERE column_name < > XYZ_value returns all rows that are not XYZ_value and 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, I recommend that you always use the IS NULL form and keep ANSI_NULLS ON.
Documentation:
The NULL is the indefinite, indeterminate and indefinable. It is not false, but neither is it true. Neither 8 nor 80. It is not a value, but a lack of one. Because it is not a value, it can be considered a status . A state of undefined.
By having this definition, when comparing NULL using standard comparators (equal, different from, greater than, less than, greater than, less than equal) will always result in false .
SELECT NULL = 0 // => false
SELECT NULL != 0 // => false
SELECT NULL = NULL // => false
Everything that compares to NULL , is false. This is explained by the fact that NULL is the absence of value, the undefined and the lack of a value.
To understand better, I like to say that it evaluates to false because it can not be said to be true.
Think of a hypothetical table in a database that holds records of people:
+----+-------+-------+
| ID | Nome | Idade |
+----+-------+-------+
| 1 | João | NULL |
| 2 | Maria | 19 |
| 3 | José | NULL |
+----+-------+-------+
Then, I ask the following questions for the database:
How old is Maria? 19.
How old is John? I do not know.
Is Mary and John the same age? I can not say that. Neither false, nor true.
The same happens (3) with the question "Are John and Joseph the same age?".
That's why comparisons with NULL need special treatment. And then the IS NULL appeared.