Каков наиболее эффективный способ написать инструкцию select с подзапросом "не в"?

Ответ 1

"Самый эффективный" будет отличаться в зависимости от размеров таблиц, индексов и т.д. Другими словами, он будет отличаться в зависимости от конкретного случая, который вы используете.

Есть три способа, которыми я обычно использую, чтобы выполнить то, что вы хотите, в зависимости от ситуации.

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 необходимо индексировать в обеих таблицах.

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. Проверяя значения NULL в предложении where, вы можете фильтровать все, что не соответствует.

Каждый из этих вариантов может работать более или менее эффективно в различных сценариях.

Ответ 2

Во-первых, ссылка на старую статью в моем блоге о том, как предикат NOT IN работает в SQL Server (и в других системах тоже):

• Подсчет отсутствующих строк: SQL Server

Вы можете переписать его следующим образом:

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

однако, большинство баз данных будут обрабатывать эти запросы одинаково.

Оба этих запроса будут использовать какой-то ANTI JOIN.

Это полезно для SQL Server, если вы хотите проверить два или более столбца, так как SQL Server не поддерживает этот синтаксис:

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

Обратите внимание, однако, что NOT IN может быть сложным из-за того, как он обрабатывает значения NULL.

Если Held.Orders имеет значение NULL, никакие записи не найдены, а подзапрос возвращается, но один NULL, весь запрос ничего не возвращает (оба IN и NOT IN будут оцениваться в NULL в этом случае).

Рассмотрим эти данные:

Orders:

OrderID
---
1

HeldOrders:

OrderID
---
2
NULL

Этот запрос:

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

ничего не вернет, что, вероятно, не так, как вы ожидали.

Однако этот:

SELECT  *
FROM    Orders o
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    HeldOrders ho
        WHERE   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

будет использовать условие фильтра, которое необходимо будет оценить и отфильтровать все соответствующие строки, которые могут быть численными

Метод ANTI JOIN, используемый как IN, так и EXISTS, просто должен убедиться, что запись не существует один раз для каждой строки в Orders, поэтому она сначала устранит все возможные дубликаты:

• NESTED LOOPS ANTI JOIN и MERGE ANTI JOIN будут просто пропускать дубликаты при оценке HeldOrders.
• A HASH ANTI JOIN устраняет дубликаты при построении хеш-таблицы.

Ответ 3

Вы можете использовать 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

Ответ 4

Я не уверен, что является наиболее эффективным, но другие варианты:

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

Ответ 5

Try

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