mysql - 这个内部连接语句是否正确？

Question

新列表 - 20,000 条数据

记录列表 - 200,000 条数据

我想显示从newlist到recordlist的所有匹配记录

它必须在 FIRSTNAME,MIDDLENAME,LASTNAME 中匹配

SELECT new.FIRSTNAME,new.MIDDLENAME,new.LASTNAME 
FROM newlist as new 
INNER JOIN recordlist as bgc ON new.FIRSTNAME = bgc.FIRSTNAME 
AND new.MIDDLENAME = bgc.MIDDLENAME AND new.LASTNAME = bgc.LASTNAME";

这个查询是否正确？显示所有匹配项？

Answer 1

您索引了我假设的三列？如果不这样做:)

当它们已经被编入索引时:

对于多个 OR，有时最好拆分查询并使用 Union 连接结果集。这是为了使用索引。

SELECT new.FIRSTNAME,new.MIDDLENAME,new.LASTNAME 
FROM newlist as new 
INNER JOIN recordlist as bgc ON new.FIRSTNAME = bgc.FIRSTNAME 
UNION
SELECT new.FIRSTNAME,new.MIDDLENAME,new.LASTNAME 
FROM newlist as new 
INNER JOIN recordlist as bgc ON new.MIDDLENAME = bgc.MIDDLENAME 
UNION
SELECT new.FIRSTNAME,new.MIDDLENAME,new.LASTNAME 
FROM newlist as new 
INNER JOIN recordlist as bgc ON new.LASTNAME = bgc.LASTNAME"