886

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.

DineshDB
  • 5,998
  • 7
  • 33
  • 49
Bob The Janitor
  • 20,292
  • 10
  • 49
  • 72
  • 7
    See also: http://stackoverflow.com/questions/7884/testing-for-inequality-in-t-sql – Dinah May 12 '09 at 15:54
  • 1
    Portability of code. If your requirements are easily met by ANSI SQL, then its better to use it. You can use the same code in all DB's. Eg. An SQL book author who wants to illustrate basic SQL using sample code. – Steam Aug 29 '13 at 22:41
  • 2
    I'd like to add an example where having only ANSI SQL code can be a problem - Standard SQL supports the options NULLS FIRST and NULLS LAST to control how NULLs sort, but T-SQL doesn’t support this option. – Steam Sep 03 '13 at 06:28
  • 1
    There no need for reopening. The marked question is a duplicate, just extended by yet one more option, `NOT (A = B)`. – TLama Nov 20 '13 at 18:27
  • 1
    @Steam, you should specify which year's version of ansi sql exactly you are refering to. Some of these version even require you to specify the level compatability or the exact parts of the standart. Which of them introduced NULLS FIRST and NULLS LAST? – Gherman Dec 16 '15 at 16:58
  • 2
    Because it reminds you of Visual Basic is kind of a bad reason. This question can certainly do without that opinion in it. A better reason would be like the reason in one of the answers where SQL is being stored in XML. Not sure why one would want to store SQL in XML, but it's a better reason nonetheless. – Michael Z. May 20 '21 at 05:00
  • In certain places where T-SQL is embedded in XML, HTML, and other markup languages it is preferable to use "!=" even though it is not ANSI, as long as you intend to stay in the TSQL realm (Microsoft stack) – JosephDoggie Mar 31 '22 at 12:17
  • Why not use `/=`? – jarlh Jul 19 '23 at 19:55

14 Answers14

788

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: <>
Snoregon Roosein
  • 3
  • 2
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
591

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

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
DBAndrew
  • 6,860
  • 2
  • 20
  • 17
  • 53
    I had always preferred to use `!=` because of its existence in every C-influenced language I have used, and because the [Python documentation](http://docs.python.org/reference/expressions.html#notin) says: "The forms `<>` and `!=` are equivalent; for consistency with C, `!=` is preferred; where `!=` is mentioned below `<>` is also accepted. The `<>` spelling is considered obsolescent." But SQL is not Python! – Iain Samuel McLean Elder Nov 04 '11 at 01:22
  • 28
    I like to use <> because it reminds me of XML. But SQL is not XML! – Rob Grant Jun 13 '14 at 05:29
  • 46
    Yes; Microsoft themselves recommend using `<>` over `!=` specifically for ANSI compliance, e.g. in Microsoft Press training kit for 70-461 exam, "Querying Microsoft SQL Server", they say "As an example of when to choose the standard form, T-SQL supports two “not equal to” operators: <> and !=. The former is standard and the latter is not. This case should be a nobrainer: go for the standard one!" – Matt Gibson Oct 12 '15 at 12:49
  • 15
    I like <> because it's easier to type. – user2023861 Jun 08 '18 at 15:57
  • 2
    I always prefered != over <> because != is consistent with every single programming language I know, I know programmers who first started using SQL and whenever they see a <> they go what is that?? – Yunfei Chen Oct 28 '20 at 19:01
118

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

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Adam Lassek
  • 35,156
  • 14
  • 91
  • 107
  • I have see mysql sql use it as well – Bob The Janitor Apr 06 '09 at 21:05
  • 5
    Can we keep calling such a widespread extension to the standard language a *proprietary* one ? At this point, it just seems the standard should be updated to require, or at least allow both syntaxes. – Johan Boulé May 23 '17 at 12:35
  • 5
    @JohanBoule well, there is a written standard for SQL and to my knowledge `!=` is not a part of it. Even though for all practical purposes it is a defacto standard, we shouldn't confuse what are and are not standard features. – Adam Lassek May 23 '17 at 21:47
  • It says not not SQL-92 standard that's more than 25 years old, so I highly doubt SQL-92 is even used today...... @JohanBoulé – Yunfei Chen Oct 28 '20 at 18:58
46

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.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Mehrdad Afshari
  • 414,610
  • 91
  • 852
  • 789
  • 1
    You do know that the 92 standard is more than 25 years old right..... If you are writing SQL using 25 year old standard, I am a bit worried about your code honestly..... – Yunfei Chen Oct 28 '20 at 18:59
  • 3
    @Yunfei Chen The point is not that people are likely to be writing in a system that *only* supports things included in the ANSI/SQL 92 standard. The point is that things included in the ANSI/SQL 92 standard have broader consistent coverage in database systems than things that are not included in the standard. That's enough reason to prefer "<>". If someone created a new DBMS that your existing database code might be ported to one day, and it only supported one of the two operators - it's far more likely to be "<>" than "!=". – Richard Abey-Nesbit Nov 18 '20 at 03:19
  • 1
    If you're porting DB code between DBMS's changing != to <> is going to be the least of your problems... – Kemuel Sanchez Dec 28 '21 at 23:49
40

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

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

Jibin Balachandran
  • 3,381
  • 1
  • 24
  • 38
Justin Niessner
  • 242,243
  • 40
  • 408
  • 536
25

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

Jibin Balachandran
  • 3,381
  • 1
  • 24
  • 38
Kyle
  • 375
  • 1
  • 4
  • 6
24

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

Kols
  • 3,641
  • 2
  • 34
  • 42
madcolor
  • 8,105
  • 11
  • 51
  • 74
  • That's SQL Server specific. Granted he asks about SQL Server, but can you find an ANSI spec reference to see if it's guaranteed to be portable for those of us who like to know that sort of thing? – Joel Coehoorn Apr 06 '09 at 21:00
  • 6
    @Joel Coehoorn, if you very port your T-SQL code "<>" and "!=" will be the least of your worries!! – KM. Apr 06 '09 at 21:10
  • 1
    Porting isn't the issue - it's when, as a developer, you're required to go back and forth between environments. Consistency is good. – Mark Ransom Apr 06 '09 at 21:28
16

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

Jibin Balachandran
  • 3,381
  • 1
  • 24
  • 38
Fat Albert
  • 193
  • 1
  • 2
  • 8
    why not just CDATA it? what happens when your query contains XML? – Janus Troelsen Sep 08 '12 at 18:30
  • 4
    what happens when someone needs a query with a less-than comparison? Or any other XML reserved symbol? This is the oddest reason I have ever heard for preferring one over the other. What about when you are writing C code, do you have a preference for operators that can be expressed in XML without escaping? – dan carter Dec 07 '21 at 22:36
  • Thinking about `<>` as "less than, greater than" can be a good thing here. SQL Server can use an index seek for inequality queries (expressed either way) by breaking it into two index range seeks (less than x and greater than x) – Martin Smith Feb 11 '23 at 20:13
13

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

Steve
  • 8,469
  • 1
  • 26
  • 37
10

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

Karl
  • 3,312
  • 21
  • 27
4

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.

Kols
  • 3,641
  • 2
  • 34
  • 42
jitendrapurohit
  • 9,435
  • 2
  • 28
  • 39
  • 7
    I'm not sure if this function would perform as well as `<>` with respect to index usage in all corner cases. Besides, the readability is certainly much worse... – Lukas Eder Dec 31 '14 at 09:05
  • 1
    As I mentioned in the answer, this worked for me on the date fields back there in 2014. Not sure what the clause/condition was which restricted other answers, but looking at some of the upvotes, this seems to be helping others too. – jitendrapurohit Jan 05 '18 at 03:26
  • 2
    You were probably comparing dates that had a time component. using CAST(date1) AS DATE <> CAST(date2) AS DATE is a better way to go IMHO – Kemuel Sanchez Jan 04 '22 at 16:15
2

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.

Andrea Antonangeli
  • 1,242
  • 1
  • 21
  • 32
-1

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.

Jkyle Landicho
  • 83
  • 6
-7

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!

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Hamlet Javier
  • 27
  • 1
  • 26
    If you run two similar queries one after the other in SQL Server, it will likely have cached data in memory and optimized for similar queries. If you did it in the reverse order, you might find the opposite result! – codemonkey Apr 25 '14 at 22:59
  • 4
    This is also wrong, they wouldn't have two operators that functioned exactly the same and one was slower "just 'cause". There are many contributing factors as to why the same query will produce different execution times. – Elliot Chance Feb 18 '15 at 00:52
  • 3
    Just look at the execution plans to see if they are different – Michael Z. May 20 '21 at 04:49
  • See [Authoritative source that <> and != are identical in performance in SQL Server](https://dba.stackexchange.com/questions/155650/authoritative-source-that-and-are-identical-in-performance-in-sql-server/155670#155670) – Martin Smith Aug 09 '23 at 04:59