在T-SQL中，我应该使用!=还是表示不相等?

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

我看到SQL同时使用!=和<>表示不相等。首选语法是什么?为什么?

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

我喜欢!=，因为<>让我想起了Visual Basic。

14 个解决方案

#1

446

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.

从技术上讲，如果使用SQL Server，即T-SQL，它们的功能是相同的。如果您在存储过程中使用它，那么没有任何性能理由使用其中一个。然后归结为个人偏好。我更喜欢使用<>，因为它是ANSI兼容的。

You can find links to the various ANSI standards at...

你可以在……找到各种ANSI标准的链接。

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

#2

650

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

大多数数据库支持!=(流行的编程语言)和<> (ANSI)。

Databases that support both != and <>:

同时支持!=和<>的数据库:

MySQL 5.1: != and <>
MySQL 5.1: !=和<>
PostgreSQL 8.3: != and <>
和<>
SQLite: != and <>
SQLite:! =和< >
Oracle 10g: != and <>
Oracle 10g: !=和<>
Microsoft SQL Server 2000/2005/2008/2012/2016: != and <>
Microsoft SQL Server 2000/2005/2008/2012/2016: !=和<>
IBM Informix Dynamic Server 10: != and <>
IBM Informix Dynamic Server 10: !=和<>。
InterBase/Firebird: != and <>
视觉火鸟:! =和< >
Apache Derby 10.6: != and <>
Apache Derby 10.6: !=和<>
Sybase Adaptive Server Enterprise 11.0: != and <>
Sybase Adaptive Server Enterprise 11.0: !=和<>

Databases that support the ANSI standard operator, exclusively:

只支持ANSI标准操作符的数据库:

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

#3

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

'<>'来自于SQL-92标准和'!='是专有的T-SQL操作符。它也可以在其他数据库中使用，但是由于它不是标准的，您必须根据具体情况使用它。

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.

在大多数情况下，您将知道要连接到哪个数据库，因此这不是真正的问题。最坏的情况下，您可能需要在SQL中进行搜索和替换。

#4

The ANSI SQL Standard defines <> as the "not equal to" operator,

ANSI SQL标准将<>定义为“不等于”操作符，

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt (5.2 <token> and <separator>)

http://www.programb.andrew.cmu.edu/ ~shadow/sql/sql1992.txt (5.2 and )

There is no != operator according to the ANSI/SQL 92 standard.

根据ANSI/SQL 92标准，没有!=操作符。

#5

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

<>是SQL-92标准下的有效SQL。

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

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

#6

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

对于SQL Server，它们都是有效的，

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

#7

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

似乎微软自己更喜欢<>而不是!=，这在他们的表约束中得到了证明。我个人更喜欢使用!=因为我清楚地读到“不等于”，但是如果您输入[field1 != field2]并将其保存为一个约束，那么下次您查询它时，它将显示为[field1 <> field2]。这告诉我正确的方法是<>。

#8

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

!=尽管不是ansi，但它更符合SQL作为可读语言的精髓。尖叫声不平等。<>说它对我来说(小于，大于)这很奇怪。我知道它的目的是小于或大于因此不相等，但这是一种非常复杂的表达方式。

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.

我只需要使用一些长SQL查询，并将它们可爱地放到XML文件中，因为有很多愚蠢的原因我不会深入讨论。

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.

只要说XML在<>下没有任何问题，我必须将它们改为!=，并在riggedy破坏我自己之前检查一下。

#9

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

您可以在T-SQL中使用任何您喜欢的。文件上说它们的功能是一样的。我更喜欢!=，因为它读起来和我(基于C/ c++ / c#的)头脑“不一样”，但是数据库专家似乎更喜欢<>。

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

我理解C语法!=在SQL Server中是由于它的Unix传统(早在Sybase SQL Server时代，Microsoft SQL Server 6.5之前)。

#11

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操作符，如果两个参数在Microsoft文档中为NULLIF，则该操作符返回NULL。因此，我认为WHERE子句可以修改为<>和!=

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.

正如我所发现的，在某些情况下，使用<>和!=并不适用于日期。因此，使用上面的表达式是必要的。

#12

-1

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.

我更喜欢使用!=而不是<>，因为有时我使用语法来编写SQL命令。在这种情况下，使用!=可以更方便地避免语法错误。

#13

-3

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!

它们都在T-SQL中被接受。然而，使用<>比!=要快得多。我刚刚运行了一个使用的复杂查询，平均运行时间为16秒。我将它们更改为<>，查询现在平均运行时间约为4秒。这是一个巨大的进步!

#14

-8

Although they function the same way, != means exactly "not equal to", while <> means greater than and less than the value stored.

虽然它们的功能是一样的，!=的意思是“不等于”，而<>的意思是大于和小于存储的值。

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

这也取决于数据库系统读取值!=和<>的方式。数据库提供者可能只是对它进行快捷操作，使它们的功能相同，因此这两种方法都没有任何好处。PostgreSQL和SQL Server不设置此快捷方式;它是按上面显示的方式读取的。

#1

446