你可以使用以下查询结构:

SELECT t1.name FROM table1 t1 JOIN table2 t2 ON t2。Fk_id != t1.id;

表1:

表二:

输出:

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

你可以在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 * FROM Table1 WHERE
id NOT IN (SELECT 
        e.id
    FROM
        Table1 e
            INNER JOIN
        Table2 s ON e.id = s.id);

从概念上讲是:在子查询中获取匹配的记录，然后在主查询中获取不在子查询中的记录。