CODEWITHSUNDEEP
sql.netoracle-database
peter
peter

Reputation: 8652

how to find the difference between two tables in oracle?

I have two tables named A and B where most of the columns are different and the common column is name. Now I want to find the records which are extra in table A based on the common field name. How to get these?

One more thing here we have to check is a few names in table B have words like 'dummy_','Test_' on the beginning which we have to trim. Say for example table A is having name ='Div_text_col_tar' and B is having name ='dummy_Div_text_col_tar' which actually the same. So we have to replace 'dummy_' and 'Test_' from the beginning of names. How to do it?

I tried like shown below without any luck:

SELECT *
FROM A t1
WHERE NOT EXISTS
  (SELECT 1
  FROM B t2
  WHERE t1.name = REGEXP_SUBSTR(t2.name,'[^-dummy_|-Test_]+',1,1)
  )
AND t1.status =100
AND t1.floor IN ('1','2','3')

Upvotes: 0

Views: 206

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

I think I would go for:

SELECT t1.*
FROM A t1
WHERE NOT EXISTS (SELECT 1
                  FROM B t2
                  WHERE t2.name IN (t1.name, 'dummy_' || t1.name, 'Test_' || t1.name)
                 ) AND
      t1.status = 100 AND
      t1.floor IN (1, 2, 3);  -- presumably, these are numbers, not strings

This seems simpler and easier to follow than using regular expressions.

Upvotes: 1

Related Questions