编写带有“not in"的 select 语句的最有效方法是什么?子查询? [英] What is the most efficient way to write a select statement with a "not in" subquery?

问题描述

编写类似于下面的选择语句的最有效方法是什么.

What is the most efficient way to write a select statement similar to the below.

SELECT *
FROM Orders
WHERE Orders.Order_ID not in (Select Order_ID FROM HeldOrders)

要点是，当项目不在另一个表中时，您需要一个表中的记录.

The gist is you want the records from one table when the item is not in another table.

推荐答案

最高效"将根据表大小、索引等而有所不同.换句话说，它会根据您使用的具体情况而有所不同.

"Most efficient" is going to be different depending on tables sizes, indexes, and so on. In other words it's going to differ depending on the specific case you're using.

根据具体情况，我通常使用三种方法来完成您想要的操作.

There are three ways I commonly use to accomplish what you want, depending on the situation.

1.如果 Orders.order_id 已编入索引，并且 HeldOrders 相当小，则您的示例工作正常.

2.另一种方法是相关子查询"，它与您所拥有的内容略有不同...

SELECT *
FROM Orders o
WHERE Orders.Order_ID not in (Select Order_ID 
                              FROM HeldOrders h 
                              where h.order_id = o.order_id)

注意添加 where 子句.当 HeldOrders 具有大量行时，这往往会更好地工作.Order_ID 需要在两个表中建立索引.

Note the addition of the where clause. This tends to work better when HeldOrders has a large number of rows. Order_ID needs to be indexed in both tables.

3.我有时使用的另一种方法是左外连接...

SELECT *
FROM Orders o
left outer join HeldOrders h on h.order_id = o.order_id
where h.order_id is null

当使用左外连接时，当有匹配行时，h.order_id 将有一个匹配 o.order_id 的值.如果没有匹配的行，h.order_id 将为 NULL.通过检查 where 子句中的 NULL 值，您可以过滤所有不匹配的内容.

When using the left outer join, h.order_id will have a value in it matching o.order_id when there is a matching row. If there isn't a matching row, h.order_id will be NULL. By checking for the NULL values in the where clause you can filter on everything that doesn't have a match.

这些变体中的每一个都可以或多或少地在各种情况下有效地工作.

Each of these variations can work more or less efficiently in various scenarios.

这篇关于编写带有“not in"的 select 语句的最有效方法是什么?子查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！