diff between tables
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 |c开发者_如何学JAVA3
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
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.
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)
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'
);
精彩评论