152

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?

Improve this question
2

4 Answers 4

Reset to default
137

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

!= is easier to store in an XML file!
Is IS NOT equivalent / synonomous / .. to <> and != ? Can all three be used the same way?
@nuttyaboutnatty, IS NOT is not equivalent to <>/!=. If I remember well, you get different behaviours when testing NULL against IS/IS NOT vs =/!=
60

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

Improve this answer

4 Comments

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

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

Improve this answer

4 Comments

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

According to this article, != performs faster

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

Improve this answer

1 Comment

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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.