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
I change employers often enough to want to match standards accepted across platforms, APIs, and applications. Since the ” operator is in the SQL-92 Standard and ANSI compliance, I use the ” operator. In “C”, I used ‘!=’, but starting with C++, ” was the accepted norm. C# uses ‘!=’, so when writing embedded SQL in C#, I match the host standard, ‘!=’.
I use because the keys are right next to each other and both need the shift state. It’s easier!
Is there any diffence between using the operator > and the operator >=, in terms of performance?
Example: WHERE date > ‘2014-12-31’ | WHERE date >= ‘2015-01-01’
Thanks in advance!
Nuno
I would love to know the answer to this one too. Great question.
I don’t think there is any performance difference unless you have a partitioned table. If you have a table partitioned by month it is possible that one of those would trigger partition pruning. It will depend on what variant of SQL you are using and what version. It is more common in older SQL versions for the where clause to match the partition range exactly in order to prune unneeded partitions. So if you have a partition with a range of 2015-01-01 to 2015-02-01, date >= 2015-01-01 should always prune your partitioned table where date > 2014-12-31 may not.
Results could be different if data is DateTime
E.g 2014-12-31 13:30:30
> is little faster than >=
If we think with big database then assuming that one table having 1 million rows of data
1) using > it will check one condition at one loop ( one million checking time )
2) Using >= ti will check tow conditions in the same loop ( tow millions checking time )
I’ll use operator “” as this is supported by the SQL standard and not dependent for a particular vendor (like SQL Server,Oracle etc) so as a result this operator can be used across all the databases
I usually use the because it is the first symbol I learned for NOT EQUAL TO when I started programing. However, there are some programming languages where only the != symbol is recognized and I am forced to adapt.
I have been using for long time (almost 24 years). However, if you are using and script deployment tool(e.g. liquibase), are not allowed because they are HTML tags. So I had to switch to !=.
Hello,
I have a question, what is the difference between ” operator and ‘NOT IN’ when comparing data on a varchar field in terms of performance.
Thanks in advance!
I would normally agree but I JUST ran into an example where returned no results BUT != returned results. I was SHOCKED! I usually use . The field I was checking was a BIGINT key field? It was WEIRD!
I prefer != to as I believe that is more intuitive. It is also closer to the ≠ that is how you would write it in mathematics.
I wonder if it really should be !== instead? If “==” is equality, then maybe “!==” would be the non-equality?
This is for languages where they separate “=”, “==”, and maybe even “===”….