ChatGPT解决这个技术问题 Extra ChatGPT

Is there any difference between "!=" and "<>" in Oracle Sql?

I would like to know if there are any differences in between the two not equal operators <> and != in Oracle.

Are there cases where they can give different results or different performance?

possible duplicate of Oracle Not Equals Operator
Possible duplicate of Oracle Not Equals Operator

a
a_horse_with_no_name

No there is no difference at all in functionality. (The same is true for all other DBMS - most of them support both styles):

Here is the current SQL reference: https://docs.oracle.com/database/121/SQLRF/conditions002.htm#CJAGAABC

The SQL standard only defines a single operator for "not equals" and that is <>


!= is easier to store in an XML file!
Is IS NOT equivalent / synonomous / .. to <> and != ? Can all three be used the same way?
Q
Quassnoi

Actually, there are four forms of this operator:

<>
!=
^=

and even

¬= -- worked on some obscure platforms in the dark ages

which are the same, but treated differently when a verbatim match is required (stored outlines or cached queries).


And variations like NOT(x = y), maybe !(x = y), etc?
Interesting! I did not know about the the ^= (saw it myself the first time when I posted the link to the manual). But your point about cached queries is a good one.
@Dems: in Oracle, boolean is not a first-class type in SQL (which is different from PL/SQL). I. e. you can't SELECT 1 = 1 FROM dual like in some other systems. So booleans have their own set of operators valid only in logical contexts (WHERE or HAVING or similar clauses). NOT is the only boolean negation operator in Oracle's SQL (AFAIK).
¬= -- worked on some obscure platforms in the dark ages - yeah, they were called "IBM mainframes". From the days when men were men, women were women, dinosaurs roamed the earth, and computers were water-cooled. :-)
佚名

At university we were taught 'best practice' was to use != when working for employers, though all the operators above have the same functionality.


The SQL standard (only) defines <> as the "not equals" operator. So I would consider using that as the "best practice"
Interesting. May have to check everything else I have been taught is of SQL standard or not. Thanks for pointing it out.
Probably my C heritage coming out, but I can't stand <> and prefer !=. Mainly because <> in its saying "less than or greater than", to me, seems to assume the datatype has an implicit ordering (which is not necessarily true, although it is true for all the SQL datatypes), whereas != is saying "not equal" in a very pure sense.
Coding standards are often employer dependent. If your employer doesn't have a coding standard, it's a good idea for the team to pick a public one.
u
user5063308

According to this article, != performs faster

http://www.dba-oracle.com/t_not_equal_operator.htm


Although popular, that website is unfortunately not a reliable source on many topics. This issue was discussed previously here. Despite the bounty, nobody was able to create a test case demonstrating a noticeable difference in performance. But my offer still stands - I'll give you a 500 point bounty if you can create a test case showing that != is faster than other not-equals operators.