你可以在mssql中使用EXCEPT或在oracle中使用MINUS，它们是相同的:

http://blog.sqlauthority.com/2008/08/07/sql-server-except-clause-in-sql-server-is-similar-to-minus-clause-in-oracle/

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

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/

SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL

问:这里发生了什么?

答:从概念上讲，我们从table1中选择所有行，对于每一行，我们尝试在table2中为name列找到具有相同值的行。如果没有这样的行，我们就把结果的table2部分留给该行。然后，通过只选择结果中匹配行不存在的行来限制选择。最后，我们忽略结果中的所有字段，除了name列(table1中我们确定存在的字段)。

虽然它可能不是在所有情况下性能最好的方法，但它应该适用于几乎所有试图实现ANSI 92 SQL的数据库引擎

以上所有查询在大表上都非常慢。战略需要改变。这里有我用于我的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

首先定义表的别名，如t1和t2。 然后得到第二个表的记录。 然后使用where条件匹配记录:

SELECT name FROM table2 as t2
WHERE NOT EXISTS (SELECT * FROM table1 as t1 WHERE t1.name = t2.name)