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?
- !=
- <>
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)
59 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.
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
I am using because of my old practice back from the days of SQLSERVER 6.5
!= is not a Standard one ,but is standard. so i am using .
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.
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.
I do not use either, because neither is SARGABLE.
!=
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.
in Joes2Pros Rick suggest to use !=
to make it different from front end applicaion
I use because != is not standard. All nonstandard operators have standard alternatives (such as instead of !=). When writing SQL I stick to the standards.
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.
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 .
i use operator for not equal to ……
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.
” is from the SQL-92 standard, ‘!=’ is a proprietary T-SQL operator
(opening and closing angular bracket) is from the SQL-92 standard, ‘!=’ is a proprietary T-SQL operator
which one is faster to execute the process??
Can you explain what would happen when ” is used..
Query below:
select * from sales where sales_name ”;
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;
There is a difference..
!= has 1 condition and has 2
* ” has two
Johnlery – I didn’t understand.