Oracle - != and <> in a query producing different results

Question

So I have a table that looks like this:

NAME      COMP_DATE

Joe       1/25/2022 4:59:59.837000 pm
Steve     1/31/2022 12:15:45.234224 PM 
James     1/15/2022 3:15:45.5434000  PM
Austin    1/17/2022 2.15.14.334000 PM

Now here is the thing ...I have a query that looks like this .

Select * from Table where comp_date != sysdate - 10.     

I was expecting the results to include joe, steve , james..and for some reason Joe is not coming through.

However when I change this query to look like this

Select * from Table where comp_date <> sysdate - 10.

I am getting all the right results.

I don't understand why that would be ? Whats the difference between != and <>

Answer 1

Explain Plan is your helper in such cases of doubt.

It shows in the Predicate Information block the exact predicate that Oracle uses.

Here the results for your two queries limited to the predicate information

For <>

EXPLAIN PLAN  SET STATEMENT_ID = 'neq' into   plan_table  FOR
select * from table_name where comp_date <> sysdate -10;

SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'neq','ALL'));

Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("COMP_DATE"<>SYSDATE@!-10)

For !=

EXPLAIN PLAN  SET STATEMENT_ID = 'neq' into   plan_table  FOR
select * from table_name where comp_date != sysdate -10;

     
SELECT * FROM table(DBMS_XPLAN.DISPLAY('plan_table', 'neq','ALL'));

Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("COMP_DATE"<>SYSDATE@!-10)

What you can clearly see it that Oracle uses only the <> operator internally.

So no difference is expected.

Despite this exercise, you may also make a thought experiment only.

You know that sysdate is of a DATE data type , i.e. without second precision. Your timestamps have second precision.

So a not equal comparison will always be true. Compare a TIMESTAMP with a DATE conveverst the DATE in a TIMESTAMP (without second precision) as you can see in the example

EXPLAIN PLAN  SET STATEMENT_ID = 'neq' into   plan_table  FOR
select * from table_name where comp_date != to_date('2022-01-25 16:59:59','yyyy-mm-dd hh24:mi:ss') - 10;

Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("COMP_DATE"<>TIMESTAMP' 2022-01-15 16:59:59')

So recheck your setup and try to repaet the result. Maybe the cause it as trivial as that the table was not filled at the time of the check, comp_date was NULL etc.

Answer 2

Whats the difference between != and <>

There is no difference; the !=, ^= and <> operators are functionally equivalent.

If you have the table:

CREATE TABLE table_name (NAME, COMP_DATE) AS
SELECT 'Joe',    TIMESTAMP '2022-01-25 16:59:59.837000' FROM DUAL UNION ALL
SELECT 'Steve',  TIMESTAMP '2022-01-31 12:15:45.234224' FROM DUAL UNION ALL
SELECT 'James',  TIMESTAMP '2022-01-15 15:15:45.543400' FROM DUAL UNION ALL
SELECT 'Austin', TIMESTAMP '2022-01-17 14:15:14.334000' FROM DUAL;

Then:

Select * from Table_name where comp_date != TIMESTAMP '2022-02-04 16:59:59.837000' - 10.

And:

Select * from Table_name where comp_date <> TIMESTAMP '2022-02-04 16:59:59.837000' - 10.

Both output all 4 rows. This is because TIMESTAMP - NUMBER is not a valid operation but DATE - INTEGER is a valid operation so the TIMESTAMP is implicitly cast to a DATE and the fractional seconds component is lost and the values are not equal.

The query is effectively:

Select *
from   Table_name
where  comp_date <> CAST(
                      CAST(
                        TIMESTAMP '2022-02-04 16:59:59.837000'
                        AS DATE
                      )
                      - 10.
                      AS TIMESTAMP
                    )

But if you subtract an interval data type rather than a number:

Select *
from   Table_name
where  comp_date != TIMESTAMP '2022-02-04 16:59:59.837000' - INTERVAL '10' DAY

and

Select *
from   Table_name
where  comp_date <> TIMESTAMP '2022-02-04 16:59:59.837000' - INTERVAL '10' DAY

Then both queries return the rows:

NAME	COMP_DATE
Steve	2022-01-31 12:15:45.234224000
James	2022-01-15 15:15:45.543400000
Austin	2022-01-17 14:15:14.334000000

There is no difference in the two queries.

db<>fiddle here

Answer 3

Although there is no difference between != and <>, there are rare cases where changing any character in a query can cause problems.

Check the execution plan for both queries and pay special attention to the "Note" section. It's possible that only one of your queries has a plan management feature that is influencing the execution plan. If the plans are different, one of them will probably have something like this in the Note section:

Note
-----
   - SQL profile "PROFILE_TEST" used for this statement

A different execution plan shouldn't ever cause different results. But maybe only one of your execution plans is running into a wrong-results bug or is using a corrupt index or table.

This is a one-in-a-million scenario. So it's more likely that there's a logical mistake somewhere in the query.