表之间的差异 [英] diff between tables

问题描述

我有两个表，它们具有相同的结构，例如：表 first的列为 a， b， c和表 second的列相同，如何查找这两个表之间的区别
当然，我可以在python上创建一些脚本，该脚本将使set（a）-set（b）生效，但是我认为在mysql中有一些方法可以实现。

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

推荐答案

您可以尝试外部联接。例如，您可以在表第一个中找到当前行，但在表第二个中不存在这样的行（未经测试）：

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

联接为您提供了一个包含所有包含在<$中的行的表c $ c> first ，例如：

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

现在，您只需要查询 second的行。aIS NULL 可以找到 second 。

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.

这篇关于表之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！