如何比较两个数据表

http://coolshell.cn/articles/925.html

有些时候，我们可能想要比较一下两个数据表，以找到其中不同的数据。比如，在进行数据移植的时候，或是在合并数据的时候，或是在比对验证数据的时候。当然比较两个表，需要这两个表结构是一样的。

我们先假设一下有如下表结构：

1 2 3 4 5 6 7 8

CREATE TABLE jajal (      user_id integer NOT NULL ,      first_name character varying (255),      last_name character varying (255),      grade character (1),      CONSTRAINT jajal_pkey PRIMARY KEY (user_id) )

然后，我们有两张表——jajal和jajal_copy，其内容如下：

 jajal

user_id	first_name	last_name	grade
1	Some	Dude	A
2	Other	Guy	B
3	You are	Welcome	B
4	What	Other	A
5	INeed	You	C
6	Mixed	Nuts	Z
7	Kirk	Land	B
8	Bit	Shooter	A
9	Sun	Microsystem	C
10	Extra	Fancy	B

jajal_copy

user_id	first_name	last_name	grade
1	Some	Dude	A
2	Other	Guy	B
3	You are	Welcome	B
4	What	Other	A
5	INeed	You	C
6	Mixed	Nuts	C
7	Kirk	Land	B
8	Bit	Shooter	A
9	Sun	Microsystem	C
10	Extra	Fancy	B

 

要比较这两张表的数据，找出不一样的数据行。我们可以使用outer join 技术。我给outer join做了一个链接，是Wikipedia的，如果你对这个技术不是很清楚，还请你行看看其技术细节。

下面是具体的SQL语句：

使用FULL OUTER JOIN

1 2 3 4 5 6 7 8 9 10 11

SELECT       * FROM       jajal j       FULL OUTER JOIN jajal_copy jc ON jc.first_name = j.first_name       AND jc.last_name = j.last_name       AND jc.grade = j.grade       AND jc.user_id = j.user_id WHERE       j.user_id IS NULL       OR jc.user_id IS NULL

运行结果如下：

user_id	first_name	last_name	grade	user_id	first_name	last_name	grade
[NULL]	[NULL]	[NULL]	[NULL]	6	Mixed	Nuts	C
6	Mixed	Nuts	Z	[NULL]	[NULL]	[NULL]	[NULL]

 

使用NATURAL FULL OUTER JOIN

关于natural join，你可以看看Wikipedia是怎么说的。

1 2 3 4 5 6 7 8

SELECT         * FROM         jajal j         NATURAL FULL OUTER JOIN jajal_copy jc WHERE         j.user_id IS NULL         OR jc.user_id IS NULL

运行结果如下：

user_id	first_name	last_name	grade
6	Mixed	Nuts	C
6	Mixed	Nuts	Z

 

MySQL SQL 代码

MySQL 并不支持 FULL OUTER JOIN，但是我们可以使用LEFT JOIN 和 RIGHT JOIN 来实现这一功能。如下所示。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

SELECT * FROM jajal j LEFT JOIN jajal_copy jc ON jc.first_name = j.first_name AND jc.last_name = j.last_name AND jc.grade = j.grade AND jc.user_id = j.user_id WHERE jc.user_id IS NULL UNION ALL SELECT * FROM jajal j RIGHT JOIN jajal_copy jc ON jc.first_name = j.first_name AND jc.last_name = j.last_name AND jc.grade = j.grade AND jc.user_id = j.user_id WHERE j.user_id IS NULL

或者你更喜欢NATURAL JOIN 版本

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SELECT * FROM jajal j NATURAL LEFT JOIN jajal_copy jc WHERE jc.user_id IS NULL UNION ALL SELECT * FROM jajal j NATURAL RIGHT JOIN jajal_copy jc WHERE j.user_id IS NULL