MySQL 比较mysql中的两个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17994449/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Compare two table in mysql
提问by nermik
I have two tables. One table (table1) has 28500 rows and the other (table2) has 17450 rows. I would like to compare these tables and find rows that do not exist in table1.
我有两张桌子。一个表 (table1) 有 28500 行，另一个 (table2) 有 17450 行。我想比较这些表并找到 table1 中不存在的行。
SELECT * FROM table1 WHERE ID NOT IN (SELECT DISTINCT(ID) FROM table2)
Any suggestions?
有什么建议？
回答by Joe Taras
Try this:
尝试这个：
SELECT table1.*
FROM table1
LEFT OUTER JOIN table2
ON table1.id = table2.id
WHERE table2.id IS NULL
LEFT OUTER JOINlink two table starting by table1, if table2 has no linked row all fields of table2 will be null. So, if you put in your WHEREcondition table2.id is null, you get only rows in table1 not existing in table2
LEFT OUTER JOIN链接两个以 table1 开头的表，如果 table2 没有链接行，则 table2 的所有字段将为空。所以，如果你把你的WHERE条件 table2.id 设为空，你只会得到 table1 中不存在于 table2 中的行
回答by Menios
You could solve this by doing a left outer join and checking for all rows that don't exist. Try the following depending on if you want to find values not existent from table1 in table2 or table2 in table1.
您可以通过执行左外连接并检查所有不存在的行来解决此问题。根据您是要查找 table2 中的 table1 或 table1 中的 table2 中不存在的值，请尝试以下操作。
SELECT *
FROM table1
LEFT OUTER JOIN table2 ON (table1.id = table2.id)
WHERE table2.id IS NULL;
SELECT *
FROM table2
LEFT OUTER JOIN table1 ON (table1.id = table2.id)
WHERE table2.id IS NULL;
SQL Fiddle: http://sqlfiddle.com/#!2/a9390/8
SQL 小提琴：http://sqlfiddle.com/#!2/a9390/8
回答by Sourav Basak
Try this:
尝试这个：
SELECT id, name
FROM (
SELECT id, name FROM table1
UNION ALL
SELECT id, name FROM table2
) tbl
GROUP BY id, name
HAVING count(*) = 1
ORDER BY id;
Suppose you have two tables table1 and table2 with same columns and values. If any one table has any extra row then using below query you can get the unmatched results.
假设您有两个具有相同列和值的表 table1 和 table2。如果任何一张表有任何额外的行，则使用以下查询可以获得无与伦比的结果。
回答by praveenraj4ever
Make use of this query:
使用此查询：
SELECT
*
FROM
table2
LEFT JOIN
table1
ON
table2.primary_key = table1 .primary_key
WHERE
table1 .primary_key IS NULL
;
回答by 131
well, if you want the answer in PHP, then here is it:
好吧，如果你想在 PHP 中得到答案，那么这里是：
$sql=mysql_query("SELECT * FROM table1");
while($row=mysql_fetch_array($sql))
{
$id=$row['id'];
$sql2=mysql_query("SELECT * FROM table2 WHERE id='$id'");
$check=mysql_num_rows($sql2);
if($check==0)
{
echo $id." is not in table1<br>";
}
}
I hope this help you
我希望这对你有帮助
回答by IncreMan
If you want to compare 2 tables by all columns (full compare, not just by single specific column like ID) you can use this approach:
如果要按所有列比较 2 个表（完全比较，而不仅仅是按 ID 等单个特定列），您可以使用以下方法：
SELECT column1, column2, column3
FROM
(
SELECT t1.column1, t1.column2, t1.column3
FROM t1
UNION ALL
SELECT t2.column1, t2.column2, t2.column3
FROM t2
) t
GROUP BY column1, column2, column3
HAVING COUNT(*) = 1
ORDER BY column3
Based on example from: http://www.mysqltutorial.org/compare-two-tables-to-find-unmatched-records-mysql.aspx
基于以下示例：http: //www.mysqltutorial.org/compare-two-tables-to-find-unmatched-records-mysql.aspx
