1

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.

4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.