mySQL - 比较表行 [英] mySQL - Compare Table Rows

问题描述

我只是不太确定这样做的正确方法。我基本上有2个表。两个表具有相同的结构。数据库1（当前数据平均20k行），数据库2（数据的累加，在任何给定时间可以超过1密耳行）。

I'm just not too sure the proper way of doing this. I basically have 2 tables. Both table have the same structure. Database1 (Current data average 20k rows), Database2 (accumlation of Data, can go over 1 mil row at any given time).

我的结果从database1不存在在Database2与特定的时间戳查询。

I want to compare both tables and give me result from Database1 that does NOT exist in the Database2 with a specific timestamp query. What I have and tried yet query takes too long to search.

尝试：

select distinct player from Database1
where not exists (select player from Database2 where snap = 1340981695)

SELECT Database1.player FROM Database1
INNER JOIN Database2 ON Database1.player NOT IN (Database2.player) AND Database2.snap = 1340981695
GROUP BY Database1.player

select distinct Database1.player from Database1
left join Database2 on Database1.player not in (Database2.player)
and Database2.snap = 1340981695

头包裹在这。感谢您的协助。

I still cannot get my head wrapped around this. Thanks for assistance.

推荐答案

有三种方法。大致顺序为性能，从最好到最差：

There are three approaches. In approximate order of performance, from best to worst:

1. 使用外连接：

1. Use an outer join:

SELECT Database1.*
FROM   Database1
  LEFT JOIN Database2
         ON Database1.id = Database2.id AND Database2.snap = 1340981695
WHERE  Database2.id IS NULL

使用 IN ：

SELECT *
FROM   Database1
WHERE  id NOT IN (SELECT id FROM Database2 WHERE snap = 1340981695)

使用 EXISTS ：

SELECT *
FROM   Database1
WHERE  NOT EXISTS (
         SELECT *
         FROM   Database2
         WHERE id = Database1.id AND snap = 1340981695
       )

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