Should I use != or <> for not equal in TSQL?

Question

I have seen SQL that uses both != and <> for not equal. What is the prefigured syntax and why?

I like != because <> reminds me of Visual Basic.

Answer 1

<> is the valid SQL according to the SQL-92 standard.

http://msdn.microsoft.com/en-us/library/aa276846(SQL.80).aspx

Answer 2

They're both valid and the same with respect to SQL Server,

http://msdn.microsoft.com/en-us/library/ms190296.aspx

Answer 3

'<>' is from the SQL-92 standard, '!=' 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.

Answer 4

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.

Answer 5

You can use whichever you like in TSQL, the docs say they both function the same way. I prefer != because it reads "not equal" to my (C/C++/C# based) mind, but DB gurus seem to prefer <>.

Answer 6

Technically they function the same if you’re using MS SQL 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...

http://en.wikipedia.org/wiki/SQL

Answer 7

I understand that the C syntax != is in SQL Server due to its UNIX heritage (back in the Sybase SQL Server days, pre MSSQL 6.5)

Answer 8

For what it's worth, here's a roundup of a bunch of popular database vendors and their support for != versus <> as the inequality operator:

Most implementations support the ANSI standard operator <> as well as the != operator that is familiar to users of most popular programming languages.

• MySQL 5.1: supports both != and <>
• PostgreSQL 8.3: supports both != and <>
• SQLite: supports both != and <>
• Oracle 10g: supports both != and <>
• Microsoft SQL Server 2000/2005/2008: supports both != and <>
• IBM Informix Dynamic Server 10: supports both != and <>
• InterBase/Firebird: supports both != and <>

The last two support only the ANSI standard operator:

• IBM DB2 UDB 9.5: supports only <>
• Apache Derby: supports only <>

Answer 9

!=, 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.

Answer 10

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

Answer 11

I believe <> is the standard but I like !=, they behave the same so I think it's a personal preference thing, like not not putting {} on a one like If statment