9 Replies Latest reply on Jul 15, 2008 2:21 AM by SamB

# Difference between two tables

Hi,

How can I find count difference between two tables,

ex: table1 count is - 150, table 2 count is - 100

What is the query for finding out the difference between these two counts, like 150 - 100 = 50

• ###### 1. Re: Difference between two tables
You'd want to use a MINUS, i.e.
``````SELECT column_list
FROM table1
MINUS
SELECT column_list
FROM table2``````
will give you the 50 rows of table1 that don't exist in table2.

Justin
• ###### 2. Re: Difference between two tables
SQL> SELECT COUNT(1) FROM EMP;

COUNT(1)
----------
15

SQL> SELECT COUNT(1) FROM DEPT;

COUNT(1)
----------
4

SQL> SELECT COUNT(1) FROM EMP
2 MINUS
3 SELECT COUNT(1) FROM DEPT
4 /

COUNT(1)
----------
15

I Think he needs 11 as OUTPUT ..
• ###### 3. Re: Difference between two tables
I have already tried using MINUS...its not giving the correct difference.

I need the output as Data boy mentioned..

Message was edited by:
user544463
• ###### 4. Re: Difference between two tables
column_list would be a list of columns. Not a COUNT(*) function, i.e.
``````SELECT *
FROM table1
MINUS
SELECT *
FROM table2``````
assuming that both tables have the same set of columns.

I'm assuming that you want the 11 rows that are in one table and not in the other.

Justin
• ###### 5. Re: Difference between two tables
If you need the number 11, and not the 11 rows that are different, you'd just want
``````SELECT a.cnt - b.cnt
FROM (SELECT COUNT(*) cnt FROM table1) a,
(SELECT COUNT(*) cnt FROM table2) b``````
Justin
• ###### 6. Re: Difference between two tables
Removed - sql error
• ###### 7. Re: Difference between two tables
Thank you!!!
• ###### 8. Re: Difference between two tables
One More

select count(distinct e.empno)-count(distinct d.deptno) from emp e ,dept d

:)
• ###### 9. Re: Difference between two tables
Though if these table are large you may want to go with Justin's solution,
```SQL> explain plan for SELECT /*+ all_rows */ a.cnt - b.cnt
2    FROM (SELECT COUNT(*) cnt FROM emp) a,
3        (SELECT COUNT(*) cnt FROM dept) b
4  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

---------------------------------------------------------------------
| Id  | Operation             |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 4 |
|   1 |  MERGE JOIN CARTESIAN |             |     1 |    26 |     4 |
|   2 |   VIEW                |             |     1 |    13 |     2 |
|   3 |    SORT AGGREGATE     |             |     1 |       |       |
|   4 |     TABLE ACCESS FULL | EMP         |    82 |       |     2 |
|   5 |   FIRST ROW           |             |       |       |       |
|   6 |    VIEW               |             |     1 |    13 |     2 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   7 |     SORT AGGREGATE    |             |     1 |       |       |
|   8 |      TABLE ACCESS FULL| DEPT        |    82 |       |     2 |
---------------------------------------------------------------------

Note: cpu costing is off, PLAN_TABLE' is old version

16 rows selected.

SQL> explain plan for select /*+ all_rows */ count(distinct e.empno)-count(distinct d.deptno) from e
mp e ,dept d
2  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

---------------------------------------------------------------------
| Id  | Operation             |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 166 |
|   1 |  SORT GROUP BY        |             |     1 |    26 |       |
|   2 |   MERGE JOIN CARTESIAN|             |  6724 |   170K|   166 |
|   3 |    TABLE ACCESS FULL  | EMP         |    82 |  1066 |     2 |
|   4 |    BUFFER SORT        |             |    82 |  1066 |   164 |
|   5 |     TABLE ACCESS FULL | DEPT        |    82 |  1066 |     2 |
---------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Note: cpu costing is off, PLAN_TABLE' is old version

13 rows selected.```