Difference between two identical tables in mysql

Question

I've two tables that are defined exactly the same but have different number of rows. There are five fields that are partial keys of the tables(which is the primary key for both the tables). I wanted to find the rows that are in one table but no the other. I tried using the exists as follows but didn't work.

select * from table1 where not exists (select * from table2)

These tables have the exact same create statement but have difference in the number of rows. I don't know if it is possible to find the difference by using joins. Thanks!

Is there the possibility that any of the columns contain `NULL` values? — Gordon Linoff, Sep 21 '18 at 19:23

score 1 · Accepted Answer · edited Sep 21 '18 at 19:22

1

Try the LEFT JOIN ... IS NULL pattern.

SELECT a.* 
  FROM table1 a
  LEFT JOIN tableb b 
        ON a.f1 = b.f1 
       AND a.f2 = b.f2
       AND a.f3 = b.f3
       AND a.f4 = b.f4
       AND a.f5 = b.f5
 WHERE b.f1 IS NULL

This works because LEFT JOIN, when finding no matching row in b, returns the data from a in its resultset, but returns NULLs for the values in b.

The ON condition looks hairy. But the query planner knows the fields are indexed, so it does the right thing.

edited Sep 21 '18 at 19:22

Gordon Linoff

1,242,037
58
646
786

answered Sep 21 '18 at 18:46

O. Jones

103,626
17
118
172

It didn't work. It returns an empty set. Do you know any other way? Thanks! – Sandesh Tiwari Sep 21 '18 at 19:13
@SandeshTiwari This should work. Maybe the extra rows are in tableb, not tablea? – Barmar Sep 21 '18 at 19:19
1

@SandeshTiwari . . . Try again. There was a typo in the `where` clause. – Gordon Linoff Sep 21 '18 at 19:23
It worked ..i was writing a instead of b in the where clause ..Thanks everyone! – Sandesh Tiwari Sep 21 '18 at 19:33

Difference between two identical tables in mysql

1 Answers1