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