24

There are two not equals operator - != and <>.

What's the difference between them? I heard that != is more efficient than other for comparing strings. Could anyone give a qualitative comment on this statement.

Wilfred Hughes
  • 29,846
  • 15
  • 139
  • 192
kuriouscoder
  • 5,394
  • 7
  • 26
  • 40
  • If i had to guess, I would say one is equivalence vs equality, like the difference between `!=` and `.equals()` in java. But that's just a guess. – corsiKa Nov 03 '10 at 17:28
  • Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production With the Partitioning option JServer Release 8.1.7.4.1 - Production SQL> select * from dual where 1!=2; D - X – N. Gasparotto Nov 03 '10 at 19:25
  • 1
    sorry, cannot format properly the output. But it was already there before 9i. – N. Gasparotto Nov 03 '10 at 19:28
  • 3
    `<>` is more efficient: to type `!=` you have to hold Shift, press `!`, then release Shift, press `=`; to type `<>` you only need to hold Shift, type `<` and `>`, and then release Shift - resulting in faster coding! jk ;) – Jeffrey Kemp Nov 04 '10 at 01:14
  • 2
    `<>` is defined by the SQL standard, `!=` is an extension that most DBMS also understand. If you want to write standard compliant SQL, use `<>` –  Jul 18 '13 at 07:13

6 Answers6

31

They are the same (as is the third form, ^=).

Note, though, that they are still considered different from the point of view of the parser, that is a stored outline defined for a != won't match <> or ^=.

This is unlike PostgreSQL where the parser treats != and <> yet on parsing stage, so you cannot overload != and <> to be different operators.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • they have significant performance difference in oracle 10.2 onwards. please see http://www.dba-oracle.com/t_not_equal_operator.htm – Junchen Liu Aug 17 '12 at 09:24
  • 5
    @shanyangqu - the important part to read from that link is "this note by Scott Canaan *suggests* that in Oracle 10.2, they can produce different execution plans, and hence, different execution speeds" - but in the end, the effect was not proven, and several (valid, IMO) theories that would explain the observed behaviour were presented - none of which suggest that the different syntaxes make any difference at all - e.g. that changing any part of a query may cause a re-parse of the statement, or match a different stored outline. – Jeffrey Kemp Oct 08 '12 at 05:10
  • @JeffreyKemp those guys bullied me into deleting my own answer, which I believe is correct. sigh...... – Junchen Liu Oct 08 '12 at 09:23
  • @shanyangqu: I don't see your deleted answer (normally I do see deleted answers on questions). Perhaps it was flagged as offensive for other reasons than mere incorrectness? – Jeffrey Kemp Oct 09 '12 at 06:11
  • @JeffreyKemp different thread. and it wasn't offensive. no need for that Jef http://stackoverflow.com/questions/12003127/oracle-operators – Junchen Liu Oct 10 '12 at 07:43
  • @shanyangqu, thanks, I can see your deleted question and the comments on it. BTW *I* didn't say your post was offensive, I merely suggested it as a possible reason why I couldn't see your post. No need to get huffy. – Jeffrey Kemp Oct 10 '12 at 07:51
  • @shanyangqu: guys, could you please move this discussion to the chat? – Quassnoi Oct 10 '12 at 08:16
6

There is no functional or performance difference between the two. Use whichever syntax appeals to you.

It's just like the use of AS and IS when declaring a function or procedure. They are completely interchangeable.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
4

They are the same, but i've heard people say that Developers use != while BA's use <>

Catfish
  • 18,876
  • 54
  • 209
  • 353
4

As everybody else has said, there is no difference. (As a sanity check I did some tests, but it was a waste of time, of course they work the same.)

But there are actually FOUR types of inequality operators: !=, ^=, <>, and ¬=. See this page in the Oracle SQL reference. On the website the fourth operator shows up as ÿ= but in the PDF it shows as ¬=. According to the documentation some of them are unavailable on some platforms. Which really means that ¬= almost never works.

Just out of curiosity, I'd really like to know what environment ¬= works on.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
1

Developers using a mybatis-like framework will prefer != over <>. Reason being the <> will need to be wrapped in CDATA as it could be interpreted as xml syntax. Easier on the eyes too.

Shrinivas
  • 64
  • 1
  • 6
-2

The difference is :

"If you use !=, it returns sub-second. If you use <>, it takes 7 seconds to return. Both return the right answer."

Oracle not equals (!=) SQL operator

Regards

Usman Maqbool
  • 3,351
  • 10
  • 31
  • 48
Jhonatan Aguilar
  • 7
  • 1