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

那对我来说很有用

SELECT * 
FROM [dbo].[table1] t1
LEFT JOIN [dbo].[table2] t2 ON t1.[t1_ID] = t2.[t2_ID]
WHERE t2.[t2_ID] IS NULL

我将在正确答案....中转发(因为我还没有酷到可以评论)以防有人觉得需要更好的解释。

SELECT temp_table_1.name
FROM original_table_1 temp_table_1
LEFT JOIN original_table_2 temp_table_2 ON temp_table_2.name = temp_table_1.name
WHERE temp_table_2.name IS NULL

在mySQL中，我看到FROM中的语法需要在表名之间使用逗号，但在sqlLite中，它似乎更喜欢空格。

底线是，当你使用不好的变量名时，它会留下问题。我的变量应该更有意义。应该有人解释一下为什么我们需要逗号或不需要逗号。

你可以在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/

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

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

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

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

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