CODEWITHSUNDEEP
sqlsql-servert-sql
Bob The Janitor
Bob The Janitor

Reputation: 20792

Should I use != or <> for not equal in T-SQL?

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.

Upvotes: 912

Views: 1234674

Answers (14)

Bill Karwin
Bill Karwin

Reputation: 562230

Most databases support != (popular programming languages) and <> (ANSI).

Databases that support both != and <>:

Databases that support the ANSI standard operator, exclusively:

  • IBM DB2 UDB 9.5: <>
  • Microsoft Access 2010: <>

Upvotes: 811

Jkyle Landicho
Jkyle Landicho

Reputation: 119

Both works and I think any difference would be very negligible! Just focus on productivity guys!

What is the value of your script? What does it do? How will it contribute to the business? Will it make more money?

Focus on these goals instead of these programmer prefered preferences. This is like which is better C# or Visual Basic languages. As if the end user cares what was used to write the App?

What the end user cares about is what your App could do. How could it help him with what he is doing.

Upvotes: 0

DBAndrew
DBAndrew

Reputation: 6958

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

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

Upvotes: 600

Andrea Antonangeli
Andrea Antonangeli

Reputation: 1262

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.

Upvotes: 2

jitendrapurohit
jitendrapurohit

Reputation: 9675

One alternative would be to use the NULLIF operator other than <> or != which returns NULL if the two arguments are equal NULLIF in Microsoft Docs. So I believe WHERE clause can be modified for <> and != as follows:

NULLIF(arg1, arg2) IS NOT NULL

As I found that, using <> and != doesn't work for date in some cases. Hence using the above expression does the needful.

Upvotes: 4

Fat Albert
Fat Albert

Reputation: 193

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

Upvotes: 16

Kyle
Kyle

Reputation: 375

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

Upvotes: 25

Justin Niessner
Justin Niessner

Reputation: 245389

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

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

Upvotes: 40

madcolor
madcolor

Reputation: 8170

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

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/not-equal-to-transact-sql-exclamation

Upvotes: 24

Steve
Steve

Reputation: 8511

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

Upvotes: 14

Karl
Karl

Reputation: 3372

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

Upvotes: 10

Hamlet Javier
Hamlet Javier

Reputation: 25

They are both accepted in T-SQL. However, it seems that using <> works a lot faster than !=. I just ran a complex query that was using !=, and it took about 16 seconds on average to run. I changed those to <> and the query now takes about 4 seconds on average to run. That's a huge improvement!

Upvotes: -8

Adam Lassek
Adam Lassek

Reputation: 35505

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

Upvotes: 121

Mehrdad Afshari
Mehrdad Afshari

Reputation: 421960

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.

Upvotes: 46

Related Questions