I have two tables TableA and TableB, I would like too compare the data of some columns and find differences like name and date columns. I tried with and without nvl

Data Sample

TableA            
id      | name      | date
1       | test a    | 29-01-2019
          

TableB            
id      | name      | date
1       | test abc  | <null>    



select a.id, a.name, a.date from TableA a
            where NOT EXISTS (select 1 from TableB b
            where  a.name = b.name OR a.date != nvl(b.date, to_date('01-01-2299', 'dd-mm-yyyy')))
              AND  a.id IN (select id from TableB)  
          

Result of this query
No result empty

Expect result like

id      | name      | date
1       | test a    | 29-01-2019
              

What is wrong with my query?

CodePudding user response：

select a.id,a.name,a.date
  from tablea a
minus
select b.id,b.name,b.date
  from tableb b

CodePudding user response：

You appear to want to find rows in TableA where there is not a corresponding row in TableB where both the id matches and one of either: the name matches; the date matches; or the date in TableB is NULL and the date in TableA matches the default.

If that is the case then:

• AND has higher precedence than OR so X OR Y AND Z is the same as X OR (Y AND Z) but you want (X OR Y) AND Z so you need to use brackets to change the precedence.
• you want = to compare the date rather than !=
• you want to directly compare the ids rather than using IN and another sub-query.

SELECT a.id,
       a.name,
       a."DATE"
FROM   TableA a
WHERE  NOT EXISTS (
         SELECT 1
         FROM   TableB b
         WHERE  (  a.name   = b.name
                OR a."DATE" = COALESCE(b."DATE", DATE '2299-01-01')
                )
         AND    a.id = b.id
       )

Which, for the sample data:

CREATE TABLE TableA (id, name, "DATE") AS
SELECT 1, 'test a', DATE '2019-01-29' FROM DUAL;

CREATE TABLE TableB (id, name, "DATE") AS
SELECT 1, 'test abc', CAST(NULL AS DATE) FROM DUAL;

Outputs:

fiddle