如何在另一个表中选择没有匹配条目的行？ [英] How to select rows with no matching entry in another table?

问题描述

我在数据库应用程序上做了一些维护工作，我发现，尽管来自一个表的值是以外键的形式使用的，但喜悦的快乐，表中没有外键约束。

我试图在这些列上添加FK约束，但是我发现，因为从以前的错误中已经有一些坏数据已经天真地纠正，我需要找到不匹配到其他表的行，然后删除它们。

我发现这种类型的一些例子在网上查询，但他们都似乎提供的例子，而不是解释，我不明白他们为什么工作。

有人可以向我解释如何构建一个查询返回所有在另一个表中没有匹配的行，以及它在做什么，这样我就可以自己做这些查询，而不是在这个 mess 中的每个表都运行到没有F K约束？

解决方案

下面是一个简单的查询：

  SELECT t1.ID 
 FROM Table1 t1 
 LEFT JOIN Table2 t2 ON t1.ID = t2.ID 
 WHERE t2.ID IS NULL 
 < code 
 
 
 
关键是：
 
 
 
 
 
 使用LEFT JOIN ;这将返回 Table1 中的所有行，而不管在 Table2 中是否有匹配的行。 > 
 
 
 
  WHERE t2.ID IS NULL 子句;这将限制仅返回到 Table2 返回的ID为null的行的结果 - 也就是说<$ c中有 NO 记录$ c> Table2 来自 Table1 的特定ID。对于 Table1 中的所有记录， Table2.ID 将返回为NULL， c> Table2 。
 
 
 
 
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.

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?
解决方案
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:

LEFT JOIN is used; this will return ALL rows from Table1, regardless of whether or not there is a matching row in Table2.
The WHERE t2.ID IS NULL clause; 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. Table2.ID will be returned as NULL for all records from Table1 where the ID is not matched in Table2.


                        
这篇关于如何在另一个表中选择没有匹配条目的行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！