Comparing values between two tables

Question

How to compare values between two different tables?

I have two tables as shown below:

Table 'A'
sql_id       OPERATION       OPTIONS 
63kf3an2j0pk SELECT STATEMENT
63kf3an2j0pkc TABLE ACCESS FULL

Table 'B'
sql_id       OPERATION     OPTIONS
63kf3an2j0pk SELECT STATEMENT
63kf3an2j0pkc INDEX UNIQUE SCAN IDX_THU

I need to compare the values in Table 'A' and Table 'B' for the given sql_id. My question is how to find the changed values.

In this case in Table 'A' the second line is TABLE ACCESS FULL. Table 'B' the the second line is INDEX UNIQUE SCAN IDX_THU. So here the value has changed. How can I find that using query? Something like when matched then "matched" when not changed "value changed".

I'm looking for output something like below:

 sql_id           comaparion outcome
dmqb1hzrf5uxh   No change in the plan
74anujtt8zw4h   plan changed

Can I do something like this?

select sql_id,case when ( select sql_id,OPERATION,options  from  a where sql_id='dmqb1hzrf5uxh'
minus
 select sql_id,OPERATION,options from  b where sql_id='dmqb1hzrf5uxh') is null then 'matched'  else 'changed' end as status;

I am comparing the explain plans before and after patch. The table 'A' is before patch result of the sqlid and table 'B' is after patch result. Now I want to compare the plans in both the tables. I want to compare the plan by using a query.

Answer 1

Assuming that sql_id has a unique or primary key constraint, you can do this with FULL JOIN (or LEFT JOIN if there are no new "inserts" in table B, or just JOIN if there are no inserts and no deletions):

SELECT 
    COALESCE(a.sql_id,b.sql_id) AS sql_id,
    CASE WHEN a.operation = b.operation THEN 'matched' 
         WHEN a.operation <> b.operation THEN 'value changed'
         WHEN b.operation IS NULL THEN 'a deleted'
         WHEN a.operation IS NULL THEN 'b inserted' 
    END AS status,
    a.operation AS a_operation,
    b.operation AS b_operation
FROM 
  a FULL JOIN b
      ON a.sql_id = b.sql_id ;

Answer 2

To view the differences, use the EXCEPT operator (SQL Server):

SELECT sql_id, OPERATION, OPTIONS
FROM TableA
EXCEPT
SELECT sql_id, OPERATION, OPTIONS
FROM TableB
ORDER BY sql_id

When you suspect data was deleted from TableA, you also need to reverse the tables:

SELECT sql_id, OPERATION, OPTIONS
FROM TableA
EXCEPT
SELECT sql_id, OPERATION, OPTIONS
FROM tableB
UNION
SELECT sql_id, OPERATION, OPTIONS
FROM TableB
EXCEPT
SELECT sql_id, OPERATION, OPTIONS
FROM TableA
ORDER BY sql_id

Answer 3

Try something like:

SELECT * from (    
    SELECT sql_id, operations, options AS options_a, STR(null) as options_b from table_a
    UNION
    SELECT sql_id, operations, NULL AS options_b, options as options_b from table_b 
    ) tmp
GROUP BY sql_id, operations 
HAVING( options_a != options_b
  OR    options_a IS NULL 
  OR    options_b is NULL )

This should reasonably quickly for smaller sets of data.

For larger sets you can use an outer join to table_b on the first inner query, and a NOT EXISTS in table_a clause on the second second inner query. This will allow you to drop the GROUP BY line and change HAVING to WHERE.