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

    Difference between two tables

    547466
      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

      Thanks in advance
        • 1. Re: Difference between two tables
          JustinCave
          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
            Data Boy
            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
              547466
              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
                JustinCave
                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
                  JustinCave
                  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
                    SamB
                    Removed - sql error
                    • 7. Re: Difference between two tables
                      547466
                      Thank you!!!
                      • 8. Re: Difference between two tables
                        Data Boy
                        One More

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

                        :)
                        • 9. Re: Difference between two tables
                          SamB
                          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.