SQL – Difference between != and <> Operator used for NOT EQUAL TO Operation

Here is interesting question received on my Facebook page. (On a side note, today we have crossed over 50,000 fans on SQLAuthority Facebook Fan Page).

What is the difference between != and <>Operator in SQL Server as both of them works same for Not Equal To Operator? 

Very interesting question indeed. Even though this looks very simple when I asked quite a few people if they know the answer before I decided to blog about it. The answer which I received was that it seems that many know the answer but everybody wanted to know the more about it.

Here is the answer – Technically there is no difference between != and <>. Both of them work the same way and there is absolutely no difference in terms of performance or result.

Here is the follow up question I received right I answer that there is no difference between those operator.

If != and <> both are the same, which one should be used in SQL queries?

Here is the answer – You can use either != or <> both in your queries as both technically same but I prefer to use <> as that is SQL-92 standard.

Though, many of the leading database applications supports both of the operators. For example -

  • SQL Server
  • MySQL
  • Oracle
  • SQLite
  • Sybase
  • IBM Informix
  • PostgreSQL

Here is my return question to you which one of the following operators you use for NOT EQUAL TO operation?

  1. !=
  2. <>

Please leave your answer with reason in comment field and I will publish the interesting answer as a follow up blog post with due credit.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

39 thoughts on “SQL – Difference between != and <> Operator used for NOT EQUAL TO Operation

  1. There is a difference. != is not ANSI compliant. Use for ANSI compliance.

    Many people mistakenly believe that ANSI compliance is not important. In SQL 2014, old style non-ANSI joins simply won’t work.

    Also, deprecated syntax may work, but that doesn’t mean it works well. When deprecated syntax is used, you aren’t taking advantage of all of the latest and greatest enhancements to the SQL engine. It’s important to keep up with the standards and make an effort to write good, modern code.

  2. I use .

    That’s what I was taught at school when learning maths and it stuck!

    Only since learning C# have I started using ‘!’ for ‘not’, but not in T-SQL.

  3. For Not equal to operation :

    For programming i am using front end applications for Equal (==) and Not Equal (!=)
    But in back end application SQL server using Equal (=) and Not Equal ()

  4. When any query calling from any application then it must be use [!=] as will be like HTML tag and this may be harmful during sending/receiving data to/from server.

  5. I personally use . I did notice that that when you view the XML for an SSRS .RDL file the is transformed to <> while the != is not. Using != can be useful when writing SQLfor SSRS datasets and you need to extract the SQL right out of the RDL file at a later time.

  6. Back when i was Programming C++ i used the ! operator.
    Lately i was doing a lot of VB programming and so mostly used the in my sql statements.

  7. I generally use != because it’s slightly easier to read, coming from a programming background it makes me spend slightly less time thinking about what it means. (! means not, and = means equal, whereas <> stops me for a moment.

    I still use them both though.

  8. Even though both are, technically, the same, I would use the != operator, because is too much like HTML or XML. Confusion with other apps could occur (you just never know!).

  9. I’ve been using bang-equal these many years because my first programming language (after some TRS-80 BASIC) was C (where bang is both a logical and a bitwise NOT and, to my humble mind, is a sensible prefix for the EQU operator when you’re trying to say NOT(EQU)). In T-SQL, I often use NOT(…) (e.g. NOT EXISTS, NOT(this AND that), etc.), so bang-equal again is a sensible equivalent for NOT( something EQU something). Even before angle brackets were utilized for HTML (later, XML), some parser Out There might have confused left_angle-right_angle with some other construct and not considered it to mean NEQ….

  10. I find I use both. If I am doing several “=” operators, I usually use “!=”, maybe just out of habit? However if went straight in looking for not equal, I’d use “”

  11. I always use because it is easy to use.
    ANSI stander.
    As early discuss that both work same.
    SQL is facilitate its user to use both syntax !=, .net as well as html :)

  12. Well, I’ll always leave <> alone in a SQL statement, because it is easy to read and the style of the developer using it. Personally, I like != because of my programming background. Interesting comment about it not being ANSI standard and some of those non-standards no longer work.

  13. i chose it to make my code more understandable. i.e. “” will be used when i am working with Integer values and “!=” for string operations in specific.

  14. Pingback: SQL – Difference Between INNER JOIN and JOIN | Journey to SQL Authority with Pinal Dave

  15. My response would be pretty much the same as practicaldavis. != just seems more intuitive. I’m surprised to learn it is not ANSI-compliant since it just makes more sense.

  16. I use because != is not standard. All nonstandard operators have standard alternatives (such as instead of !=). When writing SQL I stick to the standards.

  17. I usually use both the forms but it looks like starting from 2014 we will have to use .

    Hi pinal,

    can you explain about schema in a blog? Many people still have confusion with this.

  18. Why use != over , here is when I would use the first over the latter. = test are always more efficient than not tests. Does it make a big difference in this day and age? No, but it makes for easier reading to test what is something is = and then set the NOT. E.g. I use “Unknown” as the default country in the key for my database. I only want to process values that have been changed from Unknown so I say !(Country=’Unknown’) or !(State=’Unknown’)…etc. In my opinion this is way cleaner reading than .

  19. I use != because it reads as not equal to. I don’t use because it reads as less than greater than which is not nearly as clear as not equal to.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s