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). The question was about the two ways to write not equal to in SQL Server.
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.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.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.