values - sql<> vs !=
Should I use != or<> for not equal in T-SQL? (10)
I have seen
SQL that uses both
<> for not equal. What is the preferred syntax and why?
<> reminds me of
!=, despite being non-ANSI, is more in the true spirit of SQL as a readable language. It screams not equal.
<> says it's to me (less than, greater than) which is just weird. I know the intention is that it's either less than or greater than hence not equal, but that's a really complicated way of saying something really simple.
I've just had to take some long SQL queries and place them lovingly into an XML file for a whole bunch of stupid reasons I won't go into.
Suffice to say XML is not down with
<> at all and I had to change them to
!= and check myself before I riggedy wrecked myself.
<> is the valid SQL according to the SQL-92 standard.
Although they function the same way,
!= means exactly "not equal to", while
<> means greater than and less than the value stored.
<=, and this will make sense when factoring in your indexes to queries...
<> will run faster in some cases (with the right index), but in some other cases (index free) they will run just the same.
This also depends on how your databases system reads the values
<>. The database provider may just shortcut it and make them function the same, so there isn't any benefit either way. PostgreSQL and SQL Server do not shortcut this; it is read as it appears above.
I preferred using
!= instead of
<> because sometimes I use the
<s></s> syntax to write SQL commands. Using
!= is more handy to avoid syntax errors in this case.
It seems that Microsoft themselves prefer
!= 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
Most databases support
!= (popular programming languages) and
Databases that support both
- MySQL 5.1:
- PostgreSQL 8.3:
- Oracle 10g:
- Microsoft SQL Server 2000/2005/2008/2012/2016:
- IBM Informix Dynamic Server 10:
- Apache Derby 10.6:
- Sybase Adaptive Server Enterprise 11.0:
Databases that support the ANSI standard operator, exclusively:
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...
The ANSI SQL Standard defines
<> as the "not equal to" operator,
5.2 <token> and <separator>)
There is no
!= operator according to the ANSI/SQL 92 standard.
They're both valid and the same with respect to SQL Server,
You can use whichever you like in T-SQL. The documentation says they both function the same way. I prefer
!=, because it reads "not equal" to my (C/C++/C# based) mind, but database gurus seem to prefer