Re: NULL comparison failure in sql query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 12/13/04 

Date: Mon, 13 Dec 2004 08:15:05 -0600

You can SET ANSI_NULLS OFF so that NULL = NULL evaluates to true. See the
Books Online <tsqlref.chm::/ts_set-set_9rec.htm> for details.

However, the 'right' way to do this is to properly handle NULLs in your
query. SET ANSI_NULLS OFF is not SQL-92 compliant. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"prabhakar" <prabhakarnarvekar@msn.com> wrote in message 
news:e5GXttR4EHA.3376@TK2MSFTNGP12.phx.gbl...
> Hi,
> I want to compare 2 fields in a table considering the equality of the 
> fields even if NULL.
> eg:- table1 has fileds name1 and name2
>                 field1    field2
>    1            abc        abc
>    2            pqr        NULL
>    3            NULL    lmn
>    4            NULL    NULL
>
> query:-
> select * from table1 where field1 = field2;
>
> I want this query to return 2 rows (1 and 4 )but it only returns row 1. Is 
> there any setting or a method by which i get the correct results 
> considering NULLs in comparison. (without handling the is NULL condition 
> in the sql query) ?
>
>

Relevant Pages

  • Re: "Do Not Allow Null" fields suddenly accept Nulls
    ... SQL Server MVP ... > to "Not Null" are now allowing nulls. ... > and the database saves it just fine. ... > message saying the field does not accept null data. ...
    (microsoft.public.sqlserver.server)
  • Re: SomeField.oldvalue <> SomeField.value not working with null values
    ... I hate to be dense, but could you give me an example of how to use the Nz ... function in the below example to compare a date field that was null to one ... > You can't check for equality with Nulls. ...
    (microsoft.public.access.formscoding)
  • Re: sp_helpconstraint error
    ... This can happen if the column doesn't allow nulls. ... > Tibor Karaszi, SQL Server MVP ... >> Kalen Delaney ... >>> tempdb and needing to have the database option select into/bulkcopy ...
    (microsoft.public.sqlserver.server)
  • RE: IIF statement to compare two fields.
    ... However you have to watch out for nulls. ... I would like to use the IIF statement to compare two fields in Access query ... I would like to compare two fields in a query. ...
    (microsoft.public.access.queries)
  • Re: WHERE help
    ... Read about NULLs and three-value logic in Books Online: ... David Portas ... SQL Server MVP ...
    (microsoft.public.sqlserver.programming)