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? 

SQL - Difference between != and <> Operator used for NOT EQUAL TO Operation 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 (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Check Database Level (IsNullConcat) and Session Level Settings (CONCAT_NULL_YIELDS_NULL) using T-SQL
Next Post
SQL – Download Database Cheat Sheet for MongoDB, NuoDB, MySQL for FREE

Related Posts

No results found.

58 Comments. Leave new

  • 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.

    Reply
  • I asked myself the same question last year,what I found out that most databases support both and != except IBS DB2 : it supports only

    So I said to myself: well in that case I should get used to and not != since all of them support it…and just in case I had to work with DB2

    Reply
  • Nitin Pathak
    July 10, 2013 6:09 pm

    I am using because of my old practice back from the days of SQLSERVER 6.5

    Reply
  • != is not a Standard one ,but is standard. so i am using .

    Reply
  • 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.

    Reply
  • I’m working as a dot net developer. I’ve a habit to use != in vs code. The same I’m using most of the time in sql.

    Thank you.

    Reply
  • !=

    Reply
  • 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.

    Reply
  • in Joes2Pros Rick suggest to use !=
    to make it different from front end applicaion

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

    Reply
  • 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.

    Reply
  • 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 .

    Reply
  • i use operator for not equal to ……

    Reply
  • David Zokaites
    February 5, 2014 9:13 pm

    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.

    Reply
  • Yuvraj Singh Panwar
    February 13, 2014 2:11 pm

    ” is from the SQL-92 standard, ‘!=’ is a proprietary T-SQL operator

    Reply
    • Yuvraj Singh Panwar
      February 13, 2014 2:11 pm

      (opening and closing angular bracket) is from the SQL-92 standard, ‘!=’ is a proprietary T-SQL operator

      Reply
  • which one is faster to execute the process??

    Reply
  • HomeMakingWizards
    December 12, 2014 9:03 pm

    Can you explain what would happen when ” is used..
    Query below:
    select * from sales where sales_name ”;

    Reply
  • Is from the SQL-92 standard, != Is a proprietary T-SQL operator, according to ANSI/SQL 92 standard != not the SQL operator.So not all DB engines may support it and if you want to generate portable code I recommend using , because not all the keyboards used to have the exclamation ! key(A long time ago), but some DB like oracle also support ^= for Not Equal.

    Syntax:

    Use AdventureWork;
    GO
    Select ProductCategoryID, Name
    From Production.ProductCategory
    Where ProductCategoryID3 AND ProductCategoryID2;

    Reply
  • There is a difference..
    != has 1 condition and has 2

    Reply
  • * ” has two

    Reply

Leave a Reply