Q
Problem solve Get help with specific problems with your technologies, process and projects.

NULL can be your friend

I often see, but dislike, SQL that uses NOT or OR. Can nulls be two tics with no space between them and eliminate the OR?

I often see, but dislike, SQL that uses NOT or OR. For example,

WHERE (status IS NULL OR status <> 'A')

Can nulls be two tics with no space between them and eliminate the OR? For example,

WHERE status IN ('','B','C','D')

Does it vary with the DB product?

The above question was submitted by a visitor to the article NULLs in WHERE clauses can be deceptive (25 Jul 2005) by Serdar Yegulalp. The editors forwarded the question to me, and I would like to make a few comments.

Let me begin by repeating the mantra that everyone should be completely familiar with: NULL is not "equal" to anything, not even another NULL. If the tutorial or book that you are using to learn SQL did not amply and emphatically make this point, please find a new tutorial or book immediately.

Okay, now that that's out of the way . . . I have heard that there may be older versions of Oracle that actually do treat NULL and an empty string (the "two tics with no space between them" as you describe it) as equal. I can neither confirm nor deny this, but if it's true, it would be a serious exception to standard NULL behavior. Perhaps, though, this non-standard behavior pre-dates the standard. In any case, my understanding is that this is not carried forward into more recent versions.

Another source of confusion may arise in Microsoft SQL Server, which offers the SET ANSI_NULLS server option:

Syntax

SET ANSI_NULLS {ON | OFF}

Remarks

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.

What puzzles me is why Microsoft felt this server option was even necessary. The reason likely lies buried in the history of some very early version of the software, and Microsoft simply wanted to allow its customers to keep running existing non-standard queries (this is called "backwards compatibility").

In summary, my advice is simple: always use standard syntax. Always use IS [NOT] NULL. This will always produce correct results, no matter whether ANSI_NULLS is ON or OFF.

Furthermore, you cannot say:

WHERE foo IN (NULL,'B','C','D')

Well, you can actually say it, but it will not return TRUE when foo is NULL. That's because an IN list is just a short way of combining a number of Equals tests with ORs.

You can also not say:

WHERE foo IN ('','B','C','D')

Well, you can actually say that, too, but it will not return TRUE when foo is NULL. If there are any rows where foo is actually equal to an empty string, then of course it will find those, just not the NULLs.

My favourite way of dealing with the complexity of the SQL -- the "OR" situation that you mentioned in the question -- is to use COALESCE:

WHERE COALESCE(foo,'') IN ('','B','C','D')

What this COALESCE expression does is substitute an empty string whenever foo is NULL, so that the resulting value will be found in the IN list. Unfortunately it will also find actual empty strings too, which in my opinion should never be allowed in the column in the first place. You should have actual values or NULLs, and no "default" value like empty strings. But that's a different rant for a different day, eh.

This was last published in August 2005

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close