我应该使用!=或不等于T-SQL吗?

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

我已经看到了使用both !=和<>不相等的SQL。什么是首选语法，为什么?

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

因为<>让我想起了Visual Basic。

14 个解决方案

#1

440

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

640

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 <>
PostgreSQL 8.3: !=和<>。
SQLite: != and <>
SQLite:! =和< >
Oracle 10g: != and <>
Oracle 10g: !=和<>。
Microsoft SQL Server 2000/2005/2008/2012/2016: != and <>
微软SQL Server 2000/2005/2008/ 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>)

mso - fareast - font - family:宋体;mso - bidi - font - family: " times new roman "; mso - bidi - theme - font:

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

似乎微软自己更喜欢<> to !=从他们的表约束中可以看出。我个人更喜欢使用!=因为我清楚地读到“不等于”，但是如果您输入[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并没有完全低于<>，而且我必须将它们更改为!=并且在我自己被严重破坏之前检查自己。

#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 days中，pre - 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:

另一种选择是使用除<>或!=返回NULL的空值操作符，如果这两个参数在Microsoft文档中是相同的NULLIF。因此，我认为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

440