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

How to select rows with no matching entry in another table?

提问by Frosty840

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 messthat has no FK constraints?

有人可以向我解释如何构建一个查询，该查询返回另一个表中没有匹配项的所有行，以及它在做什么，以便我可以自己进行这些查询，而不是为这个混乱中的每个表运行 SO没有 FK 限制？

回答by AdaTheDev

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 JOINis used; this will return ALL rows from Table1, regardless of whether or not there is a matching row in Table2.

2. The WHERE t2.ID IS NULLclause; this will restrict the results returned to only those rows where the ID returned from Table2is null - in other words there is NOrecord in Table2for that particular ID from Table1. Table2.IDwill be returned as NULL for all records from Table1where the ID is not matched in Table2.

1. LEFT JOIN用来; 这将返回 中的所有行Table1，无论 中是否存在匹配的行Table2。

2. 该WHERE t2.ID IS NULL条款; 这会将返回的结果限制为仅返回的 IDTable2为空的那些行- 换句话说，该特定 ID 中没有记录Table2from Table1。Table2.ID对于Table1ID 在 中不匹配的所有记录，将作为 NULL 返回Table2。

回答by Ondrej Bozek

I would use EXISTSexpression since it is more powerfull, you can i.e more precisely choose rows you would like to join, in case of LEFT JOINyou have to take everything what's in joined table. Its efficiency is probably same as in case of LEFT JOINwith null test.

我会使用EXISTS表达式，因为它更强大，您可以更精确地选择要加入的行，以防万一LEFT JOIN您必须获取已连接表中的所有内容。它的效率可能LEFT JOIN与空测试的情况相同。

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

回答by Theo Voss

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_columndon't all match up with an idin table 2.

表 1 有一列要添加外键约束，但表 2 中的值foreign_key_id_column并不完全匹配id。

1. The initial select lists the ids from table1. These will be the rows we want to delete.
2. The NOT INclause in the where statement limits the query to only rows where the value in the foreign_key_id_columnis not in the list of table 2 ids.
3. The SELECTstatement in parenthesis will get a list of all the ids that are in table 2.

1. 初始选择列出了idtable1中的s。这些将是我们要删除的行。
2. NOT INwhere 语句中的子句将查询限制为仅查询中的值foreign_key_id_column不在表 2 列表中id的行。
3. SELECT括号中的语句将获得id表 2中所有s的列表。

回答by Karel

Where T2is 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.

并删除结果。

回答by Debendra Dash

Let we have the following 2 tables(salary and employee)

让我们有以下 2 个表（薪水和员工）

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

1. 使用内部连接

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

1. Using Left outer join

2. 使用左外连接

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

1. Using Full Join

3. 使用完全连接

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

回答by Luis H Cabrejo

From similar question here MySQL Inner Join Query To Get Records Not Present in Other TableI got this to work

从这里的类似问题MySQL Inner Join Query To Get Records Not Present in Other Table我得到了这个工作

SELECT * FROM bigtable 
LEFT JOIN smalltable ON bigtable.id = smalltable.id 
WHERE smalltable.id IS NULL

smalltableis where you have missing records, bigtableis where you have all the records. The query list all the records that not exist in smalltablebut exists on the bigtable. You could replace idby any other matching criteria.

smalltable是您丢失记录的地方，bigtable是您拥有所有记录的地方。该查询列出了 中不存在smalltable但存在于 中的所有记录bigtable。您可以替换id为任何其他匹配条件。

回答by Jerrychayan

I Dont Knew Which one Is Optimized (compared to @AdaTheDev ) but This one seems to be quicker when I use (atleast for me)

我不知道优化了哪一个（与 @AdaTheDev 相比）但是当我使用时，这个似乎更快（至少对我而言）

SELECT id  FROM  table_1 EXCEPT SELECT DISTINCT (table1_id) table1_id FROM table_2

If You want to get any other specific attribute you can use:

如果您想获得任何其他特定属性，您可以使用：

SELECT COUNT(*) FROM table_1 where id in (SELECT id  FROM  table_1 EXCEPT SELECT DISTINCT (table1_id) table1_id FROM table_2);

回答by Nitesh singh

You could opt for Viewsas shown below:

您可以选择视图，如下所示：

CREATE VIEW AuthorizedUserProjectView AS select t1.username as username, t1.email as useremail, p.id as projectid, 
(select m.role from userproject m where m.projectid = p.id and m.userid = t1.id) as role 
FROM authorizeduser as t1, project as p

and then work on the view for selecting or updating:

然后处理视图以进行选择或更新：

select * from AuthorizedUserProjectView where projectid = 49

which yields the result as shown in the picture below i.e. for non-matching column null has been filled in.

这产生如下图所示的结果，即对于非匹配列，已填充空值。

[Result of select on the view][1]

回答by Muhammad Parwej

SELECT * FROM First_table MINUS SELECT * FROM another

SELECT * FROM First_table MINUS SELECT * FROM another

回答by Hiren Makwana

You can do something like this

你可以做这样的事情

   SELECT IFNULL(`price`.`fPrice`,100) as fPrice,product.ProductId,ProductName 
          FROM `products` left join `price` ON 
          price.ProductId=product.ProductId AND (GeoFancingId=1 OR GeoFancingId 
          IS NULL) WHERE Status="Active" AND Delete="No"