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?
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 <>
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).
NOT(x = y)
, maybe !(x = y)
, etc?
^=
(saw it myself the first time when I posted the link to the manual). But your point about cached queries is a good one.
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.
<>
as the "not equals" operator. So I would consider using that as the "best practice"
<>
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.
According to this article, != performs faster
http://www.dba-oracle.com/t_not_equal_operator.htm
Success story sharing
IS NOT
equivalent / synonomous / .. to<>
and!=
? Can all three be used the same way?