Oracle Alternative to EXISTS EXCEPT

minusoracle

I wonder if there is an equally elegant solution for Oracle for the problem posted and answered here:

Comparing Columns that can contain NULLS – is there a more elegant way?

I can't get this solution (the one which was accepted) to work in Oracle environment.

Best Answer

This should be the Oracle equivalent to the accepted answer:

SELECT *
FROM a a1 
  JOIN b b1 
    ON a1.id = b1.id 
   AND EXISTS(SELECT * FROM a a2 where a2.id = a1.id
              MINUS 
              SELECT * FROM b b2 where b2.id = b1.id);

I have never seen a select a.* before (i.e. a select without a FROM clause using an alias instead of constant values) but I assume it maps to a co-related subquery

Note that onedaywhen's answer would work as well in Oracle (when replacing except with minus):

WITH a_minus_b AS (
  SELECT * 
  FROM A
  minus 
  SELECT *
  FROM B
)
SELECT * 
FROM a_minus_b T 
   JOIN B ON T.ID = B.ID;

SQLFiddle: http://sqlfiddle.com/#!4/3f369/1

Just for completeness: in standard SQL this could be written as:

SELECT *
FROM a 
  JOIN b 
    ON a.id = b.id 
where (a.string, a.dt, a.b, a.num) is distinct from (b.string, b.dt, b.b, b.num);

which is e.g. supported by Postgres: http://sqlfiddle.com/#!15/b431b/1

Related Question