Oracle <> , != , ^= Operators

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

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

Oracle , != , ^= operators

I have tested the performance of the different syntax for the not equal operator in Oracle. I have tried to eliminate all outside influence to the test.

I am using an 11.2.0.3 database. No other sessions are connected and the database was restarted before commencing the tests.

A schema was created with a single table and a sequence for the primary key

CREATE TABLE loadtest.load_test (
  id NUMBER NOT NULL,
  a VARCHAR2(1) NOT NULL,
  n NUMBER(2) NOT NULL,
  t TIMESTAMP NOT NULL
);

CREATE SEQUENCE loadtest.load_test_seq
START WITH 0
MINVALUE 0;

The table was indexed to improve the performance of the query.

ALTER TABLE loadtest.load_test
ADD CONSTRAINT pk_load_test
PRIMARY KEY (id)
USING INDEX;

CREATE INDEX loadtest.load_test_i1
ON loadtest.load_test (a, n);

Ten million rows were added to the table using the sequence, SYSDATE for the timestamp and random data via DBMS_RANDOM (A-Z) and (0-99) for the other two fields.

SELECT COUNT(*) FROM load_test;

COUNT(*)
----------
10000000

1 row selected.

The schema was analysed to provide good statistics.

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'LOADTEST', estimate_percent => NULL, cascade => TRUE);

The three simple queries are:-

SELECT a, COUNT(*) FROM load_test WHERE n <> 5 GROUP BY a ORDER BY a;

SELECT a, COUNT(*) FROM load_test WHERE n != 5 GROUP BY a ORDER BY a;

SELECT a, COUNT(*) FROM load_test WHERE n ^= 5 GROUP BY a ORDER BY a;

These are exactly the same with the exception of the syntax for the not equals operator (not just <> and != but also ^= )

First each query is run without collecting the result in order to eliminate the effect of caching.

Next timing and autotrace were switched on to gather both the actual run time of the query and the execution plan.

SET TIMING ON

SET AUTOTRACE TRACE

Now the queries are run in turn. First up is <>

> SELECT a, COUNT(*) FROM load_test WHERE n <> 5 GROUP BY a ORDER BY a;

26 rows selected.

Elapsed: 00:00:02.12

Execution Plan
----------------------------------------------------------
Plan hash value: 2978325580

--------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |    26 |   130 |  6626   (9)| 00:01:20 |
|   1 |  SORT GROUP BY        |              |    26 |   130 |  6626   (9)| 00:01:20 |
|*  2 |   INDEX FAST FULL SCAN| LOAD_TEST_I1 |  9898K|    47M|  6132   (2)| 00:01:14 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("N"<>5)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      22376  consistent gets
      22353  physical reads
          0  redo size
        751  bytes sent via SQL*Net to client
        459  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         26  rows processed

Next !=

> SELECT a, COUNT(*) FROM load_test WHERE n != 5 GROUP BY a ORDER BY a;

26 rows selected.

Elapsed: 00:00:02.13

Execution Plan
----------------------------------------------------------
Plan hash value: 2978325580

--------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |    26 |   130 |  6626   (9)| 00:01:20 |
|   1 |  SORT GROUP BY        |              |    26 |   130 |  6626   (9)| 00:01:20 |
|*  2 |   INDEX FAST FULL SCAN| LOAD_TEST_I1 |  9898K|    47M|  6132   (2)| 00:01:14 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("N"<>5)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      22376  consistent gets
      22353  physical reads
          0  redo size
        751  bytes sent via SQL*Net to client
        459  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         26  rows processed

Lastly ^=

> SELECT a, COUNT(*) FROM load_test WHERE n ^= 5 GROUP BY a ORDER BY a;

26 rows selected.

Elapsed: 00:00:02.10

Execution Plan
----------------------------------------------------------
Plan hash value: 2978325580

--------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |    26 |   130 |  6626   (9)| 00:01:20 |
|   1 |  SORT GROUP BY        |              |    26 |   130 |  6626   (9)| 00:01:20 |
|*  2 |   INDEX FAST FULL SCAN| LOAD_TEST_I1 |  9898K|    47M|  6132   (2)| 00:01:14 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("N"<>5)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      22376  consistent gets
      22353  physical reads
          0  redo size
        751  bytes sent via SQL*Net to client
        459  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         26  rows processed

The execution plan for the three queries is identical and the timings 2.12, 2.13 and 2.10 seconds.

It should be noted that whichever syntax is used in the query the execution plan always displays <>

The tests were repeated ten times for each operator syntax. These are the timings:-

<>

2.09
2.13
2.12
2.10
2.07
2.09
2.10
2.13
2.13
2.10

!=

2.09
2.10
2.12
2.10
2.15
2.10
2.12
2.10
2.10
2.12

^=

2.09
2.16
2.10
2.09
2.07
2.16
2.12
2.12
2.09
2.07

Whilst there is some variance of a few hundredths of the second it is not significant. The results for each of the three syntax choices are the same.

The syntax choices are parsed, optimised and are returned with the same effort in the same time. There is therefore no perceivable benefit from using one over another in this test.

"Ah BC", you say, "in my tests I believe there is a real difference and you can not prove it otherwise".

Yes, I say, that is perfectly true. You have not shown your tests, query, data or results. So I have nothing to say about your results. I have shown that, with all other things being equal, it doesn't matter which syntax you use.

"So why do I see that one is better in my tests?"

Good question. There a several possibilities:-

Your testing is flawed (you did not eliminate outside factors -
other workload, caching etc You have given no information about
which we can make an informed decision)
Your query is a special case (show me the query and we can discuss it).
Your data is a special case (Perhaps - but how - we don't see that either).
There is some other outside influence.

I have shown via a documented and repeatable process that there is no benefit to using one syntax over another. I believe that <> != and ^= are synonymous.

If you believe otherwise fine, so

a) show a documented example that I can try myself

and

b) use the syntax which you think is best. If I am correct and there is no difference it won't matter. If you are correct then cool, you have an improvement for very little work.

"But Burleson said it was better and I trust him more than you, Faroult, Lewis, Kyte and all those other bums."

Did he say it was better? I don't think so. He didn't provide any definitive example, test or result but only linked to someone saying that != was better and then quoted some of their post.

Show don't tell.

What does mean in Oracle

It means 'not equal to'. So you're filtering out records where ordid is 605. Overall you're looking for any records which have the same prodid and qty values as those assigned to ordid 605, but which are for a different order.

Oracle (+) Operator

That's Oracle specific notation for an OUTER JOIN, because the ANSI-89 format (using a comma in the FROM clause to separate table references) didn't standardize OUTER joins.

The query would be re-written in ANSI-92 syntax as:

   SELECT ...
     FROM a
LEFT JOIN b ON b.id = a.id

This link is pretty good at explaining the difference between JOINs.

It should also be noted that even though the (+) works, Oracle recommends not using it:

Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax:

Oracle Not Equals Operator

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.

NOT EQUAL operator != not working as expected in Oracle

For NULL values you have to explicitly give IS NULL or IS NOT NULL

SELECT * FROM TABLE WHERE COLUMN <> 0 OR COLUMN IS NULL;

SELECT * FROM TABLE WHERE NVL(COLUMN,1) <> 0

The Optimizer is going to do like in the first case even if you go with NVL()

What is the difference between NOT and != operators in SQL?

NOT negates the following condition so it can be used with various operators. != is the non-standard alternative for the <> operator which means "not equal".

e.g.

NOT (a LIKE 'foo%')
NOT ( (a,b) OVERLAPS (x,y) )
NOT (a BETWEEN x AND y)
NOT (a IS NULL)

Except for the overlaps operator above could also be written as:

a NOT LIKE 'foo%'
a NOT BETWEEN x AND y
a IS NOT NULL

In some situations it might be easier to understand to negate a complete expression rather then rewriting it to mean the opposite.

NOT can however be used with <> - but that wouldn't make much sense though: NOT (a <> b) is the same as a = b. Similarly you could use NOT to negate the equality operator NOT (a = b) is the same as a <> b

Not equal != operator on NULL

<> is Standard SQL-92; != is its equivalent. Both evaluate for values, which NULL is not -- NULL is a placeholder to say there is the absence of a value.

Which is why you can only use IS NULL/IS NOT NULL as predicates for such situations.

This behavior is not specific to SQL Server. All standards-compliant SQL dialects work the same way.

Note: To compare if your value is not null, you use IS NOT NULL, while to compare with not null value, you use <> 'YOUR_VALUE'. I can't say if my value equals or not equals to NULL, but I can say if my value is NULL or NOT NULL. I can compare if my value is something other than NULL.

Oracle PL/SQL where condition: not equal to 0 or null

You can simplify the condition to just:

WHERE id != 0

because comparisions with NULL (using both = or != operators) always evaluates to NULL (which is treated in SQL as "false" in conditions),

therefore NULL != 0 always evaluates to FALSE and you can skip this part in this condition

WHRE id != 0 AND id IS NOT NULL

Braiam's answer Where nvl(Id,0)<>0, although correct, will prevent database from using an index on id column, and this could have bad impact on the performce.