NULL value gives unexpected query result

With the query:
SELECT * FROM T1
WHERE F1 LIKE N'%V1%'
AND NOT F2 LIKE N'%V2%'
and F1 is like V1:
If F2 is like V2, then EOF.
If F2 is not like V2, then the record is selected.
But if F2 is NULL then the result is EOF. If F2 is NULL, and is therefore
not like V2, I would have expected the record to be selected.
Please advise. If this is the correct result, is there another way to
construct the statement to give a record if V2 is NULL?Roger,
per default setting, any judgement other than "is (not) null" evaluates to
null when the column in question has the null value. If F2 is null, it is
neither like V2 nor not like V2. It is uncertain. Therefore it does not
satisfy your condition to return the record.
So your query should be:
SELECT * FROM T1
WHERE F1 LIKE N'%V1%'
AND (NOT F2 LIKE N'%V2%' or F2 is null)
Quentin
"Roger Withnell" <RogerWithnell@.discussions.microsoft.com> wrote in message
news:9677F81D-DBB0-4B50-9FC0-9F4BDDB94F0C@.microsoft.com...
> With the query:
> SELECT * FROM T1
> WHERE F1 LIKE N'%V1%'
> AND NOT F2 LIKE N'%V2%'
> and F1 is like V1:
> If F2 is like V2, then EOF.
> If F2 is not like V2, then the record is selected.
> But if F2 is NULL then the result is EOF. If F2 is NULL, and is therefore
> not like V2, I would have expected the record to be selected.
> Please advise. If this is the correct result, is there another way to
> construct the statement to give a record if V2 is NULL?|||Many thanks for this.
An alternative would be to make all NULL fields blank ie a zero length strin
g.
Would you advise this or not?
"Quentin Ran" wrote:

> Roger,
> per default setting, any judgement other than "is (not) null" evaluates to
> null when the column in question has the null value. If F2 is null, it is
> neither like V2 nor not like V2. It is uncertain. Therefore it does not
> satisfy your condition to return the record.
> So your query should be:
> SELECT * FROM T1
> WHERE F1 LIKE N'%V1%'
> AND (NOT F2 LIKE N'%V2%' or F2 is null)
> Quentin
>
> "Roger Withnell" <RogerWithnell@.discussions.microsoft.com> wrote in messag
e
> news:9677F81D-DBB0-4B50-9FC0-9F4BDDB94F0C@.microsoft.com...
>
>|||Roger Withnell a écrit :
> Many thanks for this.
> An alternative would be to make all NULL fields blank ie a zero length str
ing.
> Would you advise this or not?
Absolutly not !!!!
An empty string does not say the same thing that nothing at all.
NULL is not a value. It is a symbolic marker wich say there is no value.
Always a NULL marker in an expression does the expression to be evaluate
to UNKNOWN value wich is neither true nor false.
Specials operators and function are available to make NULL marker
valuate, like COALESCE, CASE, IS NULL, IS NOT NULL, NULLIF...
You must use it.
What will be the result of this query :
SELECT AVG(AGE)
FROM employees
if you have marked all the AGE unknown values to 1900-01-01 ?
A +
[vbcol=seagreen]
> "Quentin Ran" wrote:
>
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************