CODEWITHSUNDEEP
sqlsql-servert-sql
Kevin Anderson
Kevin Anderson

Reputation: 79

In T-SQL, does <> '?' mean something other than data not equal to a question mark?

I've been excluding records in a query where a number of text fields only contain a question mark. My code ended thus:

AND Address1 <> '?'
AND Address2 <> '?'
AND Address3 <> '?'

Unfortunately, a lot of other records (that had NULL values in the above address fields) were also being excluded - records I actually needed. I took out Addres2 & 3, and get more records than I suspect I need, but it now works. So, my question is this: does the <>'?' part signify anything else anywhere in T-SQL?

Upvotes: 0

Views: 407

Answers (2)

Thom A
Thom A

Reputation: 95659

<> means "not equal not", so something like 'abc' <> 'def' would evaluate to TRUE. In regards to NULL, however, you can't use boolean operators. Using a boolean operator with a NULL returns the result NULL (effectively unknown), which isn't true; and (to state the obvious) a clause is only true if it's true. So, as a result something like 'abc' <> NULL = NULL != TRUE; meaning that the WHERE clause requirements are not met.

To deal with NULL you need to use the IS NULL and IS NOT NULL operators. So, for your SQL you would need:

AND (Address1 <> '?' OR Address1 IS NULL)
AND (Address2 <> '?' OR Address2 IS NULL)
AND (Address3 <> '?' OR Address3 IS NULL)

(Assuming you want rows with NULL returned).

As Sean commented, you can wrap columns like Address1 with the ISNULL or COALESCE function (i.e. ISNULL(Address1,'') <> '?'), however, I recommend against that. Using those functions within your WHERE would make your query non-SARGable and could have (severe) performance impacts on your query as a result.

Upvotes: 5

mypetlion
mypetlion

Reputation: 2524

You're correct in believing that <> means "not equal to". What you're missing out on is that NULL doesn't equal anything, and also doesn't not equal anything (not even other NULL values). Any equality comparison to NULL actually evaluates to "Unknown" which is not the same as either True or False.

NULL = '?' -- evaluates to Unknown
NULL <> '?' -- evaluates to Unknown
NULL = NULL -- evaluates to Unknown
NULL <> NULL -- evaluates to Unknown

If you want to include NULL records in your result, you need to compare using IS NULL. Change your statements to this:

AND (Address1 <> '?' OR Address1 IS NULL)
AND (Address2 <> '?' OR Address2 IS NULL)
AND (Address3 <> '?' OR Address3 IS NULL)

Upvotes: 0

Related Questions