Finding differences between tables-CodePudding

I'm attempting to calculate the difference in count between two tables on oracle sql developer, could someone provide some guidance on how this is done?

thanks in advance

Haven't tried any script

CodePudding user response：

One (nice?) option is to use both selects as CTEs, and then you have various options available, e.g.

SQL> with
  2  c1 as (select count(*) cnt from emp),
  3  c2 as (select count(*) cnt from dept)
  4  select c1.cnt,
  5         c2.cnt,
  6         c1.cnt - c2.cnt as difference
  7  from c1 cross join c2;

       CNT        CNT DIFFERENCE
---------- ---------- ----------
        14          4         10

SQL>

If you regularly gather table statistics (and tables aren't changed (inserted or deleted) in between), you might even use num_rows column of the user_tables view:

SQL> select e.num_rows emp_rows,
  2         d.num_rows dept_rows,
  3         e.num_rows - d.num_rows difference
  4  from user_tables e cross join user_tables d
  5  where e.table_name = 'EMP'
  6    and d.table_name = 'DEPT';

  EMP_ROWS  DEPT_ROWS DIFFERENCE
---------- ---------- ----------

Huh? No data? How come? Missing statistics! Let's collect them:

SQL> exec dbms_stats.gather_table_stats('SCOTT', 'EMP');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SCOTT', 'DEPT');

PL/SQL procedure successfully completed.

SQL> select e.num_rows emp_rows,
  2         d.num_rows dept_rows,
  3         e.num_rows - d.num_rows difference
  4  from user_tables e cross join user_tables d
  5  where e.table_name = 'EMP'
  6    and d.table_name = 'DEPT';

  EMP_ROWS  DEPT_ROWS DIFFERENCE
---------- ---------- ----------
        14          4         10

SQL>

Now it works.