Eliminating Nulls in tSQL

85 pts.
Tags:
ANSI
NULL
T/SQL
If I want to return null values in a query that has "Where Field1<>'test' is there an option I can turn on? Example: CREATE TABLE myTable(Field1 varchar(25)) GO INSERT INTO myTable VALUES ('test') INSERT INTO myTable VALUES('test2') INSERT INTO myTable VALUES(null) GO Select * From myTable Where Field1<>'test' --Only returns test2 and not the null record.. What if I want to return the null without using "or Field1 is null"?

Answer Wiki

Thanks. We'll let you know when a new response is added.

The <a href=”http://msdn.microsoft.com/en-us/library/ms184325.aspx”>ISNULL</a> function could be an option.

For example:

<pre>Select * From myTable
Where ISNULL(Field1,’*’)<>’test’</pre>

But this option should be avoided as this will not use any indexes that you have setup

(If you decide to use it, the replacement value should be a value that you know doesn’t exist in the data in Field1).

Try this:

Select * From myTable
except
Select * From myTable where Field1 =’test’

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
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
  • TJay
    I was hoping to aviod changing my query..is there a database option I can change to avoid this?
    85 pointsBadges:
    report
  • carlosdl
    There is the ANSI_NULLS option, which you could turn off, but I think it only applies when you compare directly to NULL, and would not make any difference in this case. On the other hand, that option will be removed in future versions, so its use should be avoided.
    69,175 pointsBadges:
    report
  • Denny Cherry
    The option of using ISNULL(Field1, '*') <> 'test' should be avoided as this will not use any indexes that you have setup. NULL is a special case when it comes to the WHERE clause as it means unknown. Because the value of the column is unknown, when a boolean operation is done comparing NULL to test the answer isn't TRUE or FALSE, but rather UNKNOWN. Carlos, where did you read that SET ANSI_NULLS is being deprecated? I've got the SQL Server 2008 BOL and it says nothing about that.
    66,075 pointsBadges:
    report
  • carlosdl
    Yes, applying functions to indexed columns will prevent the optimizer from using indexes. It would be better to add the 'or Field1 is null' condition (but that's what TJay wanted to avoid) As for the ANSI_NULLS option, maybe it's not that it is going to 'disappear', but it will be always ON ( :-) ), and will generate an error if you try to set it to OFF from an application. And I read it precisely in SQL Server 2008 BOL. Here is the link: SET ANSI_NULLS (Transact-SQL)
    69,175 pointsBadges:
    report
  • TJay
    Thanks everyone, I appretiate all of the input!
    85 pointsBadges:
    report
  • Denny Cherry
    Hugh, I'll need to check for an updated BOL for my desktop. The version included with SQL 2008 doesn't have any mention of that. I love it when the documentation doesn't match the other documentation.
    66,075 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
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

Thanks! We'll email you when relevant content is added and updated.

Following