如何选择另一个表中没有匹配条目的行？

た入场券 2022-11-25 10:04 112阅读 0赞

本文翻译自：[How to select rows with no matching entry in another table?][How to select rows with no matching entry in another table]

*I'm doing some maintenance work on a database application and I've discovered that, joy of joys, even though values from one table are being used in the style of foreign keys, there's no foreign key constraints on the tables.* **我正在对数据库应用程序进行一些维护工作，但我发现，即使一个表中的值以外键的形式使用，但表上没有外键约束。**

*I'm trying to add FK constraints on these columns, but I'm finding that, because there's already a whole load of bad data in the tables from previous errors which have been naively corrected, I need to find the rows which don't match up to the other table and then delete them.* **我正在尝试在这些列上添加FK约束，但是我发现，由于先前的错误已被天真纠正，因此表中已经存在大量不良数据，因此我需要查找那些行匹配另一个表，然后将其删除。**

*I've found some examples of this kind of query on the web, but they all seem to provide examples rather than explanations, and I don't understand why they work.* **我在网络上找到了一些这种查询的示例，但是它们似乎都提供了示例而不是说明，而且我不明白它们为什么起作用。**

*Can someone explain to me how to construct a query which returns all the rows with no matches in another table, and what it's doing, so that I can make these queries myself, rather than coming running to SO for every table in this mess that has no FK constraints?* **有人可以向我解释如何构造一个查询，该查询返回另一个表中没有匹配项的所有行，以及它在做什么，以便我可以自己进行这些查询，而不是为*混乱*中的每个表运行SO没有FK限制？**

--------------------

### \#1楼 ###

参考：[https://stackoom.com/question/h6nw/如何选择另一个表中没有匹配条目的行][https_stackoom.com_question_h6nw]

--------------------

### \#2楼 ###

*Where `T2` is the table to which you're adding the constraint:* **`T2`是要向其添加约束的表：**

SELECT *
    FROM T2
    WHERE constrained_field NOT
    IN (
        SELECT DISTINCT t.constrained_field
        FROM T2 
        INNER JOIN T1 t
        USING ( constrained_field )
    )

*And delete the results.* **并删除结果。**

--------------------

### \#3楼 ###

*I would use [`EXISTS`][EXISTS] expression since it is more powerfull, you can ie more precisely choose rows you would like to join, in case of `LEFT JOIN` you have to take everything what's in joined table.* **我将使用[`EXISTS`][EXISTS]表达式，因为它更强大，即可以更精确地选择要`LEFT JOIN`行，如果使用`LEFT JOIN` ，则必须将联接表中的所有内容都取走。** *Its efficiency is probably same as in case of `LEFT JOIN` with null test.* **其效率可能与使用空测试的`LEFT JOIN`情况相同。**

SELECT t1.ID
    FROM Table1 t1
    WHERE NOT EXISTS (SELECT t2.ID FROM Table2 t2 WHERE t1.ID = t2.ID)

--------------------

### \#4楼 ###

SELECT id FROM table1 WHERE foreign_key_id_column NOT IN (SELECT id FROM table2)

*Table 1 has a column that you want to add the foreign key constraint to, but the values in the `foreign_key_id_column` don't all match up with an `id` in table 2.* **表1有一列要向其中添加外键约束，但是`foreign_key_id_column`的值并不全部与表2中的`id`匹配。**

1.  *The initial select lists the `id` s from table1.* **初始选择列出了table1中的`id` 。** *These will be the rows we want to delete.* **这些将是我们要删除的行。**
2.  *The `NOT IN` clause in the where statement limits the query to only rows where the value in the `foreign_key_id_column` is not in the list of table 2 `id` s.* **该`NOT IN`在子句WHERE语句限制了查询资料列在价值`foreign_key_id_column`并不在表2中的列表`id`秒。**
3.  *The `SELECT` statement in parenthesis will get a list of all the `id` s that are in table 2.* **括号中的`SELECT`语句将获得表2中所有`id`的列表。**

--------------------

### \#5楼 ###

*Here's a simple query:* **这是一个简单的查询：**

SELECT t1.ID
    FROM Table1 t1
        LEFT JOIN Table2 t2 ON t1.ID = t2.ID
    WHERE t2.ID IS NULL

*The key points are:* **关键点是：**

1.  *`LEFT JOIN` is used;* **`LEFT JOIN` ；** *this will return ALL rows from `Table1` , regardless of whether or not there is a matching row in `Table2` .* **这将返回所有行`Table1` ，无论是否存在一个匹配的行`Table2` 。**
2.  *The `WHERE t2.ID IS NULL` clause;* **`WHERE t2.ID IS NULL`子句；** *this will restrict the results returned to only those rows where the ID returned from `Table2` is null - in other words there is **NO** record in `Table2` for that particular ID from `Table1` .* **这将限制返回到仅那些行，其中从返回的ID的结果`Table2`是空的-换句话说存在没有记录`Table2`从该特定ID `Table1` 。** *`Table2.ID` will be returned as NULL for all records from `Table1` where the ID is not matched in `Table2` .* **对于`Table1`中ID与`Table2`不匹配的所有记录， `Table2.ID`将返回NULL。**

--------------------

### \#6楼 ###

*Let we have the following 2 tables(salary and employee)* **让我们有以下两个表（薪水表和员工表）** [![在此处输入图片说明][hzfgb.jpg]][hzfgb.jpg]

***Now i want those records from employee table which are not in salary.*** **现在，我希望从员工表中获得那些不在薪水中的记录。** *We can do this in 3 ways:* **我们可以通过3种方式做到这一点：**

1.  ***Using inner Join*** **使用内部联接**

select * from employee
    where id not in(select e.id from employee e inner join salary s on e.id=s.id)

[![在此处输入图片说明][yOyYu.jpg]][yOyYu.jpg]

1.  ***Using Left outer join*** **使用左外部联接**

select * from employee e 
    left outer join salary s on e.id=s.id  where s.id is null

[![在此处输入图片说明][n7sOp.jpg]][n7sOp.jpg]

1.  ***Using Full Join*** **使用完全联接**

select * from employee e
    full outer join salary s on e.id=s.id where e.id not in(select id from salary)

[![在此处输入图片说明][lswIO.jpg]][lswIO.jpg]

[How to select rows with no matching entry in another table]: https://oldbug.net/q/h6nw/How-to-select-rows-with-no-matching-entry-in-another-table
[https_stackoom.com_question_h6nw]: https://stackoom.com/question/h6nw/%E5%A6%82%E4%BD%95%E9%80%89%E6%8B%A9%E5%8F%A6%E4%B8%80%E4%B8%AA%E8%A1%A8%E4%B8%AD%E6%B2%A1%E6%9C%89%E5%8C%B9%E9%85%8D%E6%9D%A1%E7%9B%AE%E7%9A%84%E8%A1%8C
[EXISTS]: https://msdn.microsoft.com/en-us/library/ms188336.aspx
[hzfgb.jpg]: https://i.stack.imgur.com/hzfgb.jpg
[yOyYu.jpg]: https://i.stack.imgur.com/yOyYu.jpg
[n7sOp.jpg]: https://i.stack.imgur.com/n7sOp.jpg
[lswIO.jpg]: https://i.stack.imgur.com/lswIO.jpg