Should I use != or <> for not equal in T-SQL?
Should I use != or <> for not equal in T-SQL?
Question
I have seen SQL that uses both != and <> for not equal. What is the preferred syntax and why?
I like !=, because <> reminds me of Visual Basic.
Accepted Answer
Technically they function the same if you’re using SQL Server AKA T-SQL. If you're using it in stored procedures there is no performance reason to use one over the other. It then comes down to personal preference. I prefer to use <> as it is ANSI compliant.
You can find links to the various ANSI standards at...
Popular Answer
Most databases support != (popular programming languages) and <> (ANSI).
Databases that support both != and <>:
- MySQL 5.1:
!=and<> - PostgreSQL 8.3:
!=and<> - SQLite:
!=and<> - Oracle 10g:
!=and<> - Microsoft SQL Server 2000/2005/2008/2012/2016:
!=and<> - IBM Informix Dynamic Server 10:
!=and<> - InterBase/Firebird:
!=and<> - Apache Derby 10.6:
!=and<> - Sybase Adaptive Server Enterprise 11.0:
!=and<>
Databases that support the ANSI standard operator, exclusively:
Read more... Read less...
'<>' is from the SQL-92 standard and '!=' is a proprietary T-SQL operator. It's available in other databases as well, but since it isn't standard you have to take it on a case-by-case basis.
In most cases, you'll know what database you're connecting to so this isn't really an issue. At worst you might have to do a search and replace in your SQL.
The ANSI SQL Standard defines <> as the "not equal to" operator,
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt (5.2 <token> and <separator>)
There is no != operator according to the ANSI/SQL 92 standard.
<> is the valid SQL according to the SQL-92 standard.
http://msdn.microsoft.com/en-us/library/aa276846(SQL.80).aspx
They're both valid and the same with respect to SQL Server,
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/not-equal-to-transact-sql-exclamation
It seems that Microsoft themselves prefer <> to != as evidenced in their table constraints. I personally prefer using != because I clearly read that as "not equal", but if you enter [field1 != field2] and save it as a constrait, the next time you query it, it will show up as [field1 <> field2]. This says to me that the correct way to do it is <>.