diff between tables

Question

I have two tables, with the same structure, for example: table "first' with columns 'a','b','c' and table 'second' with the same columns. How to find difference betweet those two tables? Of course, I can make some script on python, that will make set(a)-set(b), but I think there is some way to do it in mysql.

UPD:

Table 'first'
a   |b   |c
====|====|====
a1  |b1  |c1
a2  |b2  |c2
a3  |b3  |c3

Table 'second'
a   |b   |c
====|====|====
a2  |b2  |c2
a3  |b3  |c3
a4  |b4  |c4

the result I need is something like that:

Table 'first-second'
a   |b   |c
====|====|====
a1  |b1  |c1

Or

Table 'second-first'
a   |b   |c
====|====|====
a4  |b4  |c4

Answer 1

Have a look at MySQL Diff Tool

The accepted answer mentions a tool Toad® for MySQL that can be used.

Answer 2

difference means? differenciate the fields while writing queries ??? u can use first.a, second.a etc while writing queries.! (hope i answered ur question, if not : throw more light on the question tat i understand it better)

Answer 3

You want this:

select column_name
from information_schema.columns
where
    table_name = 'FirstTable'
    and table_schema = 'DatabaseHoldingFirstTable'
    and column_name not in (
        select column_name
        from information_schema.columns
        where table_name = 'SecondTable'
        and table_schema = 'DatabaseHoldingSecondTable'
    );

Answer 4

You could try an outer join. For example, you could find rows present in table first but absent in table second like this (not tested):

SELECT first.a, first.b, first.c FROM first LEFT JOIN second USING(a,b,c) 
WHERE second.a IS NULL

The join gives you a table containing all rows present in first, like this:

first.a first.b first.c second.a second.b second.c
   a1      b1     c1      NULL      NULL     NULL
   a2      b2     c2       a2       b2       c2

Now you only have to query for rows with second.a IS NULL to find rows absent in second.

Performance might be poor since you have to join over all columns.