Re: NULL = NULL not working

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 12/05/04 

Date: Sun, 05 Dec 2004 01:03:39 +0100

On Sat, 4 Dec 2004 15:53:05 -0800, we7313 wrote:

>I have a proc that can specify a price range if its passed in.
>my and where clause looks like this:
>
>where ((PriceViewHotelPrice.price + PriceViewAirPrice.price)between 500 and
>600) or(NULL = NULL))
>
>I have removed the parameters & replaced them with the values passed in
>running it through Sql Analyzer & have confirmed the problem.
>If I replace NULL = NULL with 'a' = 'a' it works fine. Does anyone see the
>problem with this?

Hi we7313,

This is working as designed. Using ANSI-standard behaviour for NULLs, NULL
is never equal to anything. Think of NULL as an unknown value. If you
asked you if an unknown value is equal to an unknown value, would you say
"yes", "no" or "no idea"?
Welcome in the wonderfull world of three-valued logic!

This being said, I'm not sure what you're trying to accomplish and why you
are trying to do it this way. Could you explain your actual problem
instead of presenting only a partial solution? There might be other, maybe
even better ways to solve your problem.

If you describe the problem, it would be helpfull to include table
structure (as CREATE TABLE statements, including all constraints), sample
data (as INSERT statements) and expected output - that often helps getting
across what you try to do AND it's a useful testing aid.

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: NOT IN with a subquery
    ... If there is a NULL in the list for the IN clause, ... ELSE SELECT 'UNKNOWN' ... >> This query returns 155 rows: ... >> select distinct dp_source from clients ...
    (microsoft.public.sqlserver.programming)
  • Re: airplane travel and my brand new dslr
    ... it is not unknown for high end checked gear to get stolen. ... believe the airlines have a little clause that they will not be responsible if ...
    (rec.photo.digital.slr-systems)
  • Re: DECODE
    ... it returns a value of one datatype. ... its THEN clause. ... WHEN NULL THEN 'no bar' ... The second WHEN clause is always UNKNOWN. ...
    (microsoft.public.sqlserver.programming)
  • Re: Clarification on NULL Records
    ... NULL represents a missing/unknown attribute rather than an actual value. ... result when NULLs are involved and comparisons that return UNKNOWN are ... excluded by the WHERE clause. ... Read about NULLs and three-value logic in Books Online: ...
    (microsoft.public.sqlserver.programming)
  • Re: Count function with Null values
    ... NULL represents a missing/unknown attribute rather than an actual value. ... result when NULLs are involved and comparisons that return UNKNOWN are ... excluded by the WHERE clause. ... test for the presence or absence of NULLs. ...
    (microsoft.public.sqlserver.programming)