我没有足够的代表点数来给froadie的答案投票。但我不同意对克里斯回答的评论。以下答案:

SELECT name
FROM table2
WHERE name NOT IN
    (SELECT name 
     FROM table1)

在实践中效率更高。我不知道为什么，但我对800k+的记录进行了运行，上面发布的第二个答案的优势是巨大的。只有我的0.02美元。

以下是对我最有效的方法。

SELECT *
FROM @T1
EXCEPT
SELECT a.*
FROM @T1 a
JOIN @T2 b ON a.ID = b.ID

这比我试过的其他方法快了一倍多。

SELECT <column_list>
FROM TABLEA a
LEFTJOIN TABLEB b 
ON a.Key = b.Key 
WHERE b.Key IS NULL;

https://www.cloudways.com/blog/how-to-join-two-tables-mysql/

我没有足够的代表点数来给froadie的答案投票。但我不同意对克里斯回答的评论。以下答案:

SELECT name
FROM table2
WHERE name NOT IN
    (SELECT name 
     FROM table1)

在实践中效率更高。我不知道为什么，但我对800k+的记录进行了运行，上面发布的第二个答案的优势是巨大的。只有我的0.02美元。

以上所有查询在大表上都非常慢。战略需要改变。这里有我用于我的DB的代码，你可以音译改变字段和表名。

这就是策略:创建两个隐式临时表并将它们合并。

The first temporary table comes from a selection of all the rows of the first original table the fields of which you wanna control that are NOT present in the second original table. The second implicit temporary table contains all the rows of the two original tables that have a match on identical values of the column/field you wanna control. The result of the union is a table that has more than one row with the same control field value in case there is a match for that value on the two original tables (one coming from the first select, the second coming from the second select) and just one row with the control column value in case of the value of the first original table not matching any value of the second original table. You group and count. When the count is 1 there is not match and, finally, you select just the rows with the count equal to 1.

看起来并不优雅，但它比上面所有的解决方案都要快几个数量级。

重要提示:启用要检查的列上的INDEX。

SELECT name, source, id
FROM 
(
    SELECT name, "active_ingredients" as source, active_ingredients.id as id 
        FROM active_ingredients

    UNION ALL
        
    SELECT active_ingredients.name as name, "UNII_database" as source, temp_active_ingredients_aliases.id as id 
    FROM active_ingredients
    INNER JOIN temp_active_ingredients_aliases ON temp_active_ingredients_aliases.alias_name = active_ingredients.name

) tbl
GROUP BY name
HAVING count(*) = 1
ORDER BY name

小心陷阱。如果表1中的字段Name包含null，你就会感到惊讶。 更好的是:

SELECT name
FROM table2
WHERE name NOT IN
    (SELECT ISNULL(name ,'')
     FROM table1)