Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
4  
Oracle has MINUS in place of EXCEPT. –  ypercube Apr 5 '13 at 18:05
    
still doesn't work as shown in the previous quesion when I change that keyword... error is sql command not ended properly. –  robarwebservices Apr 5 '13 at 20:26
    
Ah, yeah, because Oracle doesn't like SELECT without FROM clause. Try ... AND EXISTS(SELECT A.* FROM dual MINUS SELECT B.* FROM dual) –  ypercube Apr 6 '13 at 0:38
    
@ypercube - perhaps you could convert your comment into an answer - might be more helpful for future visitors with this question? –  Max Vernon Jun 1 at 20:55
1  
@MaxVernon I don't remember testing it then but I did now and it doesn't work. a_horse_with_no_name's answer is the way to go for Oracle. –  ypercube Jun 1 at 22:01

1 Answer 1

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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