147

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?

Mesop
  • 5,187
  • 4
  • 25
  • 42
  • 5
    possible duplicate of [Oracle Not Equals Operator](http://stackoverflow.com/questions/4089771/oracle-not-equals-operator) – Mesop May 18 '12 at 12:01
  • 1
    Possible duplicate of [Oracle Not Equals Operator](https://stackoverflow.com/questions/4089771/oracle-not-equals-operator) –  Jun 14 '17 at 17:06

4 Answers4

130

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

55

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

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 3
    And variations like `NOT(x = y)`, maybe `!(x = y)`, etc? – MatBailie May 18 '12 at 10:35
  • 1
    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. –  May 18 '12 at 10:36
  • 1
    @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). – Quassnoi May 18 '12 at 10:43
  • 3
    `¬= -- 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. :-) – Bob Jarvis - Слава Україні Dec 14 '16 at 19:21
1

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

  • 17
    The SQL standard (only) defines `<>` as the "not equals" operator. So I would consider using that as the "best practice" –  May 20 '12 at 13:10
  • 3
    Interesting. May have to check everything else I have been taught is of SQL standard or not. Thanks for pointing it out. –  May 20 '12 at 15:21
  • 13
    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. – Jeffrey Kemp May 23 '12 at 02:07
  • 1
    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. – Denise Skidmore Mar 15 '18 at 16:12
-2

According to this article, != performs faster

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

user5063308
  • 19
  • 1
  • 22
    Although popular, that website is unfortunately not a reliable source on many topics. This issue was discussed previously [here](http://stackoverflow.com/q/12003127/409172). 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. – Jon Heller Jun 30 '15 at 00:38