[RESOLVED] <> or !=

[InvisibleDuncan]

Quick question, folks: In SQL, is there any reason to prefer <> or != over the other, or is it simply personal preference? Is one standard and one non-standard?

Annoyingly, they're very hard terms to Google!

[FunkyDexter]

I'm pretty sure != isn't part of the ansii standard so may not be supported in all flavours of sql (I actually had to go and try it out in SQL Server to see if it works... it does).

It's also worth mentioning that <> does not mean "Not Equal To", it means "Is Greater Than Or Less Than". It's pedantic but those two meanings are subtly different and can kick you in the pants in some rare edge cases. I've therefore always preferred to use the NOT keyword along with an equals, ie "Where Not 1=0"

[techgnome]

It's pedantic but those two meanings are subtly different and can kick you in the pants in some rare edge cases

I'd like to hear more about such case because in over 20 years I've never run into a problem with <> not operating as expected (as a not equals) ...

I didn't know that != worked in SQL Server, good to know.

-tg

[kfcSmitty]

Just talking about MS SQL server (no idea about any others), != actually gets translated to <> on the backend, so they're the same.

[techgnome]

Well, that's what I thought too... but the comment from Funky seemed to indicate there was a subtle difference that he had encountered at some point. Since I've never encountered such a situation, I was curious to know more about it.

-tg

[Nightwalker83]

Just talking about MS SQL server (no idea about any others), != actually gets translated to <> on the backend, so they're the same.

I didn't know this.

[jmcilhinney]

I guess that, as a .NET developer using SQL Server or some other database that supports both, it might depend on whether you come from a C# or VB background. That's basically personal preference though so doesn't provide the possible technical reason that you were looking for.

[jmcilhinney]

It's also worth mentioning that <> does not mean "Not Equal To", it means "Is Greater Than Or Less Than".

Is that really true though? That's what the operator looks like but the documentation for both VB and T-SQL define it as meaning "not equal to". Maybe there is some other language where that subtle distinction does exist. I guess one scenario where it might make a difference is when dealing with NULLs. NULL is not equal to any other value, even NULL, but it is not less than or greater than. If that operator was interpreted as you say then you'd have to be aware of that when dealing with NULLs.

The T-SQL documentation for the != operator also says:

Functions the same as the <> (Not Equal To) comparison operator.

so that addresses the original question, at least in terms of T-SQL.

Microsoft have languages that use both so probably thought that supporting both in T-SQL was a good idea.

[InvisibleDuncan]

Thanks, guys. I hadn't seen != in years, but came across a whole clutch of them in a set of stored procedures written by a 3rd party. I was just wondering if they knew something I didn't.

I think I'll stick with <>, although I'm now curious about Funky's comments that it doesn't always perform as expected.

[InvisibleDuncan]

Oops - double post!

[FunkyDexter]

Wow, I wasn't expecting my comments on <> to cause such controversy.

I was talking from a purely mathematical point of view rather than a sql (ansii or flavoured) point of view. To my knowledge <> is equivalent to "Not Equal To" in TSql at least. I prefer to use NOT and = (or ==) because it's mathematically precise so avoids any language based nuances - it always has the same meaning no matter where I am.

I do have a vague memory of getting kicked in the teeth by <> once but I can't remember the context very well. I remember it was when writing client code rather than sql and I seem to remember it was to do with checking non-equivalence rather than non-equality and I think it was something to do with string comparisons. I'm pretty sure it wasn't to do with nulls though because, of course, you have to use IS to do a compare on them - in sql at least. Not =, <> or != would all give the wrong result.

Anyway, the upshot is, if you're using <> in TSQL, to my knowledge you're safe.