如何从一个表中选择另一个表中不存在的所有记录? [英] How to select all records from one table that do not exist in another table?

问题描述

table1 (id, name)
table2 (id, name)

table1 (id, name)
table2 (id, name)

查询:

SELECT name   
FROM table2  
-- that are not in table1 already

推荐答案

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

问:这里发生了什么?

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

A: Conceptually, we select all rows from table1 and for each row we attempt to find a row in table2 with the same value for the name column. If there is no such row, we just leave the table2 portion of our result empty for that row. Then we constrain our selection by picking only those rows in the result where the matching row does not exist. Finally, We ignore all fields from our result except for the name column (the one we are sure that exists, from table1).

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

While it may not be the most performant method possible in all cases, it should work in basically every database engine ever that attempts to implement ANSI 92 SQL

这篇关于如何从一个表中选择另一个表中不存在的所有记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！