sql server programming: selecting rows with null values -- sql server programming

RE: selecting rows with null values

John Bell
9/28/2005 12:00:02 AM

Hi

You may want to read Itzik's article "Donâ€™t Avoid the UNKNOWN" in the
September issue of SQL Server magazine.
http://www.windowsitpro.com/Authors/AuthorID/638/638.html

You can not say cc_status = null use

(cc_status IS NULL OR cc_status <> 'SOK')

John

[quoted text, click to view]

Re: selecting rows with null values

Razvan Socol
9/28/2005 12:04:39 AM

Hi, Meetu

We can say that "NULL is not equal to 'SOK'", but also that "NULL is
not different to 'SOK'". In fact, NULL is not equal to anything,
because it means "unknown". We can even say that "NULL is not equal to
NULL". For more informations, see:
http://msdn.microsoft.com/library/en-us/acdata/ac_8_qd_06_7xir.asp

Regarding your query, the following solutions are possible:
1. Use SET ANSI_NULLS OFF (not recommended)
2. Use: ISNULL(cc_status,'')<>'SOK'
3. Use: cc_status IS NULL or cc_status<>'SOK'

Razvan

RE: selecting rows with null values

R.D
9/28/2005 12:16:04 AM

yup
that's right: but cc_status = null may not work use cc_status is null
for update = null should be used
for select or where use is null as per ANSI-
92 standard
OR
SET ANSI_NULLS OFF
Regards
R.D
[quoted text, click to view]

[quoted text, click to view]

Re: selecting rows with null values

Meetu
9/28/2005 4:48:34 AM

Thank you David ,John,Razvan and R.D. for replying.

I find the use of isnull(cc_status,'') useful, but I didnt understand
one thing why Razvan mentioned that Set ansi_null off is not
recommended? Razvan can you elaborate on this one? Thanks

Re: selecting rows with null values

John Bell
9/28/2005 6:09:02 AM

Hi

From BOL:
The SQL-92 standard requires that an equals (=) or not equal to (<>)
comparison against a null value evaluates to FALSE. When SET ANSI_NULLS is
ON, a SELECT statement using WHERE column_name = NULL returns zero rows even
if there are null values in column_name. A SELECT statement using WHERE
column_name <> NULL returns zero rows even if there are nonnull values in
column_name.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison
operators do not follow the SQL-92 standard. A SELECT statement using WHERE
column_name = NULL returns the rows with null values in column_name. A SELECT
statement using WHERE column_name <> NULL returns the rows with nonnull
values in the column. In addition, a SELECT statement using WHERE column_name
<> XYZ_value returns all rows that are not XYZ value and that are not NULL.

There are several features that will not work when ANSI_NULLS is off such as
distributed queries and indexed views.

John

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.