What is difference between != and operators ?

Question

What is difference between != and operators ?

Amit Choudhary-216780

Old Hand

Points: 384
More actions
August 17, 2007 at 6:07 am

#117877

What is difference between != and <> operators in SQL ?
Thanks in advance
Amit Choudhary

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

stewart noble SSCrazy Points: 2009 More actions · Answer 1

According to Books Online, and an Execution Plan, there is no functional difference between the two expressions. However <> is the ANSI 92 compliant version, so I suppose it is preferable to use that one...

Hope that helps.

S

Charbel-371270 SSCrazy Points: 2260 More actions · Answer 2

as BOL they both have the same functionalities.

they prevent (not always) the query optimizer from using an index to perform a search.

i hear lot of thoughts about tu use this not that but never got a clear answer

If something's hard to do, then it's not worth doing.

steve smith-401573 SSChampion Points: 10889 More actions · Answer 3

what's the difference between slang and formal speech? You can say the same thing using either. However, computers as a rule greatly appreciate formal speech. I believe the "!=" operator has been deprecated and will fail to work in either 2005 or 2008, whereas ANSI standards generally will work as documented.

Besides the issues of readability and style, there is also a question whether you have to care about performance.

"Don't use no double negatives!" is not exactly the same as "Always use positives" - and the shades of meaning in the difference can be the difference between forcing a table scan and using an index.

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 4

> I believe the "!=" operator has been deprecated and will fail to work in either 2005 or 2008, whereas ANSI standards generally will work as documented.

> Besides the issues of readability and style, there is also a question whether you have to care about performance.

SQL 2005 BOL states that != functions the same as <>.

The latest CTP of SQL 2008 BOL states the same (it appears to be an unedited copy of the 2005 topic).

Niether topic, nor the corresponding topics on <>, make any reference to impending removal.

Furthermore, there is no mention that != will be removed from T-SQL anytime soon. The current command deprecation list is available in the "Deprecated Database Features in SQL Server 2005" topic (look for 'deprecated features [SQL Server] in the BOL index), either in BOL or here:

http://msdn2.microsoft.com/en-us/library/ms143729.aspx

Note there are headings in that topic for 'features not supported in the next version of SQL Server' and 'Features not supported in a future version of SQL Server'.

!=, !< and !> are all valid and will continue to be supported in T-SQL.

Outside of T-SQL, <> is the only supported not-equal-to operator for DMX and MDX, and != is the only supported not-equal-to operator in SSIS.

-Eddie

Eddie Wuerch
MCM: SQL

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

!= is what most GUI folks write and it's a bit more difficult to read than <>. Other than that, no difference.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Karl Hewlett Mr or Mrs. 500 Points: 528 More actions · Answer 6

> != is most GUI folks write and it's a bit more difficult to read than <>.

Totally a matter of opinion. ! mean Not (negation operator) and = means equal so != means 'not equal'; perfectly logical and (for me) the clearest syntax. However I understand that for some people with a more VB like background the other syntax (greatest than and less than) is just as clear. I think you will find it is a matter of familiarity.

I am likely biased in this but I consider the != syntax more standard; its what I learnt as a C programmer, its used in all Unix variant (commercial or Lynix), I think its used on the mainframe. The <> syntax I have only come across in some MS products, although I note one contributer tells us it is ANSI 92 compliant.

Regards

Karl

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

No... not more VB like... more mathematical like

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)