MySQL优化查询为not in

I have an excel with VBA program which connects to MySQL database to retrieve some information. Initially, when the program loads I have the below query.

我有一个用VBA编程的excel，它连接到MySQL数据库来检索一些信息。最初，当程序加载时，我有下面的查询。

SELECT A.id,A.first_name,A.last_name FROM Table1 A WHERE A.ID NOT IN(SELECT DISTINCT  
SUBJECT FROM Table2)

TABLE 1 has data like below.

表1的数据如下所示。

1 Christian Bale
1克里斯蒂安·贝尔
2 Christopher Nolan
2克里斯托弗·诺兰
etc
等

TABLE 2 has data like below.

表2的数据如下所示。

1 acted_in Batman
1 acted_in蝙蝠侠
2 directed Batman
2指导蝙蝠侠
etc
等

My query given above works perfectly fine as long as the total rows are less in table 2. However, currently my data has 26000 rows in table 1 and 102000 rows in table 2. So, when I run the above query in my program, it takes around half an hour to execute the query and sometimes it doesn't execute properly. I tried the below query too but it also takes long time.

只要表2中的总行更少，上面给出的查询就可以很好地工作。然而，目前我的数据在表1中有26000行，在表2中有102000行。因此，当我在程序中运行上述查询时，执行查询大约需要半个小时，有时查询执行不正确。我也尝试了下面的查询，但它也需要很长时间。

SELECT  A.id, A.first_name, A.last_name FROM Table1 a 
WHERE   NOT EXISTS
(
    SELECT  1
    FROM    Table2 al
    WHERE   a.id = al.subject
)

Is there an efficient way to rephrase the query?

是否有一种有效的方法来重新表达查询?

3 个解决方案

#1

Use an outer join

使用一个外部连接

SELECT A.*
FROM Table1 a
LEFT OUTER JOIN (SELECT DISTINCT subject
            FROM Table2) b ON a.id = b.subject
WHERE b.subject IS NULL

#2

Similar to the other offered answer, however the query may perform better with a direct link to the table rather than to an inline view on a SELECT DISTINCT:

与另一个提供的答案类似，但是查询可以通过与表的直接链接而不是选择不同的视图的内联视图来执行:

SELECT t1.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.id = t2.subject
WHERE t2.subject IS NULL

#3

Query:

查询:

SELECT a.*
FROM Table1 a
WHERE NOT EXISTS (SELECT subject
                  FROM Table2 b
                  WHERE a.id = b.subject)

#1