用“not in”子查询编写select语句最有效的方法是什么?

“最有效”将根据表的大小、索引等而有所不同。换言之,它将根据您使用的具体情况而有所不同。

根据具体情况,我通常使用三种方法来完成你想要的工作。

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有大量行时,这种方法会更好地工作。订单号需要在两个表中编入索引。

三。我有时使用的另一种方法是左外联接…

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将为空。通过检查WHERE子句中的空值,可以对所有不匹配的内容进行筛选。

这些变化中的每一个都可以或多或少地在不同的场景中有效地工作。

首先,我的博客中有一篇关于如何 NOT IN 谓词作用于 SQL Server (在其他系统中也是如此):

• Counting missing rows: SQL Server

您可以将其改写如下:

SELECT  *
FROM    Orders o
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    HeldOrders ho
        WHERE   ho.OrderID = o.OrderID
        )

但是,大多数数据库将对这些查询进行相同的处理。

这两个查询都将使用 ANTI JOIN .

这对 SQL服务器 如果要检查两列或多列,因为 SQL服务器 不支持此语法:

SELECT  *
FROM    Orders o
WHERE   (col1, col2) NOT IN
        (
        SELECT  col1, col2
        FROM    HeldOrders ho
        )

但是,请注意 不在 可能很棘手,因为它对待的方式 NULL 价值观。

如果 Held.Orders 可以为空,找不到任何记录,子查询只返回一个 无效的 ,整个查询将不返回任何内容(两个 IN 和 不在 将评估为 无效的 在这种情况下)。

考虑这些数据:

Orders:

OrderID
---
1

HeldOrders:

OrderID
---
2
NULL

此查询:

SELECT  *
FROM    Orders o
WHERE   OrderID NOT IN
        (
        SELECT  OrderID
        FROM    HeldOrders ho
        )

将返回 没有什么 这可能不是你所期望的。

然而,这一个:

选择*
从订单O
不存在的地方
(
选择空值
来自赫尔多德斯何
其中ho.orderid=o.orderid
)

将返回行 OrderID = 1 .

注意 LEFT JOIN 其他人提出的解决方案远不是最有效的解决方案。

此查询:

SELECT  *
FROM    Orders o
LEFT JOIN
        HeldOrders ho
ON      ho.OrderID = o.OrderID
WHERE   ho.OrderID IS NULL

将使用需要评估和筛选所有 匹配 可以是numerius的行

安 反连接 两者都使用的方法 在 和 EXISTS 只需要确保记录不存在 一旦 每行 Orders ,因此它将首先消除所有可能的重复项:

• NESTED LOOPS ANTI JOIN 和 MERGE ANTI JOIN 在评估时跳过重复项 HeldOrders .
• 一 HASH ANTI JOIN 将在生成哈希表时消除重复项。

你可以使用 LEFT OUTER JOIN 并检查 NULL 在右边的桌子上。

SELECT O1.*
FROM Orders O1
LEFT OUTER JOIN HeldOrders O2
ON O1.Order_ID = O2.Order_Id
WHERE O2.Order_Id IS NULL

我不确定什么是最有效的,但是其他的选择是:

1. Use EXISTS

SELECT * 
FROM ORDERS O 
WHERE NOT EXISTS (SELECT 1 
                  FROM HeldOrders HO 
                  WHERE O.Order_ID = HO.OrderID)

2. Use EXCEPT

SELECT O.Order_ID 
FROM ORDERS O 
EXCEPT 
SELECT HO.Order_ID 
FROM HeldOrders

尝试

SELECT *
FROM Orders
LEFT JOIN HeldOrders
ON HeldOrders.Order_ID = Orders.Order_ID
WHERE HeldOrders.Order_ID IS NULL